ITS_OP/SRC - 스마트교차로 테이블 연계 이전 소스/SQL/VMS소통정보확인.SQL

--VMS 정보제공구간 ID, 정보제공구간 ID
SELECT A.*, NVL(B.IFSC_NM, '-') AS IFSC_NM 
  FROM TB_VMS_LINK_IFSC A,                 
       TB_IFSC          B                  
 WHERE A.IFSC_ID = B.IFSC_ID(+)            
 ORDER BY A.VMS_IFSC_ID, A.ORD   



SELECT VMS_IFSC_ID, NVL(RGST_DT, '19000101000000') AS RGST_DT,     
       NVL(SPED,      0) AS SPEED, NVL(TRVL_HH,   0) AS TRVL_HH,   
       DECODE(CMTR_GRAD_CD, '1', '1',                              
                            '2', '2',                              
                            '3', '3', '0') AS CMTR_GRAD_CD         
  FROM TB_VMS_CMTRINFR                                             
 WHERE RGST_DT > TO_CHAR(SYSDATE -  10 / 1440, 'YYYYMMDDHH24MISS') 
   AND VMS_IFSC_ID IN ('304', '88')


SELECT A.VMS_IFSC_ID, A.VMS_IFSC_NM, A.DSPL_STRT_NODE_NM, A.DSPL_END_NODE_NM, 
       A.ROAD_NM, A.SPOT_NM,                                                  
       A.DETR_ID, A.AXIS_YN,                                                  
       NVL(B.RGST_DT,       '') AS RGST_DT,                                   
       NVL(B.CMTR_GRAD_CD, '0') AS CMTR_GRAD_CD,                              
       NVL(B.SPED,           0) AS SPED,                                      
       NVL(B.TRVL_HH,        0) AS TRVL_HH                                    
  FROM TB_VMS_IFSC A,                                                         
       (SELECT *                                                              
          FROM TB_VMS_CMTRINFR                                                
         WHERE RGST_DT >= TO_CHAR(SYSDATE-10/1440, 'YYYYMMDDHH24MISS') ) B    
 WHERE A.VMS_IFSC_ID = B.VMS_IFSC_ID(+)                                       
   AND A.VMS_IFSC_ID IN ('304', '88')


SELECT A.*, 
       A.FRST_VMS_IFSC_ID, A.SECD_VMS_IFSC_ID, A.THIR_VMS_IFSC_ID, A.FOUR_VMS_IFSC_ID,
       A.FRST_IMG_IFSC_ID, --1단 이미지 정보제공구간 ID
       A.SECD_IMG_IFSC_ID, --2단 이미지 정보제공구간 ID
       A.THIR_IMG_IFSC_ID, --3단 이미지 정보제공구간 ID
       A.FOUR_IMG_IFSC_ID  --4단 이미지 정보제공구간 ID
  FROM TB_VMS_TRFC_INFR_DSPL_SCH A,
       TB_VMS_CTLR B
 WHERE B.VMS_CTLR_ID = 'VMS4000001'
   AND B.VMS_CTLR_NMBR = A.VMS_CTLR_NMBR


SELECT A.VMS_CTLR_NMBR, B.VMS_IFSC_ID, B.DSPL_PRRT 
  FROM TB_VMS_CTLR      A,                         
       TB_VMS_IFSC_RLTN B,                         
       TB_VMS_IFSC      C                          
 WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR           
   AND B.VMS_IFSC_ID   = C.VMS_IFSC_ID  
   AND A.VMS_CTLR_ID = 'VMS4000001'       
 ORDER BY A.VMS_CTLR_NMBR, B.DSPL_PRRT             


DEL_YN = 'N'