ITS_OP/SRC/SQL/도로정보조회.sql

SELECT ROAD_ID, 
       ROAD_NAME, 
       DRCT_CD, 
       STRT_NM,
       END_NM, 
       DEL_YN, 
       SECT_LNGT, 
       F_NODE_ID,
       T_NODE_ID, 
       SECT_GRAD_CD, 
       AREA_CD,
       DECODE(AREA_CD, 'LAT261', 'Y', 'LAT262', 'Y', 'LAT263', 'Y', 'N') REGN_YN
  FROM TB_ROAD
 WHERE DEL_YN = 'N'
 ORDER BY ROAD_ID

SELECT ROAD_ID, IFSC_ID, ORD
  FROM TB_ROAD_IFSC_RLTN 
 ORDER BY ROAD_ID, ORD


SELECT A.ROAD_ID, PRCN_DT, TFVL, SPED, PTRN_SPED, OCPY_RATE, TRVL_HH, CMTR_GRAD_CD, DATA_NUM, 
       (CASE WHEN PRCN_DT <= TO_CHAR(SYSDATE - 10/1440, 'YYYYMMDDHH24MISS')        
             THEN 'Y' ELSE 'N' END) AS MISSING,                                    
       (CASE WHEN PRCN_DT >= TO_CHAR(SYSDATE - 15/1440, 'YYYYMMDDHH24MISS')        
             THEN 'N' ELSE 'Y' END) AS LASTANS,                                    
       (SELECT MAX(PRCN_DT) FROM TB_ROAD_TRAF) MPRCN_DT                            
  FROM TB_ROAD_TRAF A, TB_ROAD B 
 WHERE A.ROAD_ID = B.ROAD_ID