ITS_OP/SRC - 스마트교차로 테이블 연계 이전 소스/SQL/시설물 현재통신상태정보 조회.sql

SELECT * 
  FROM (SELECT WEB_CMRA_ID ID, UPDT_DT, 
               (CASE WHEN UPDT_DT > TO_CHAR(SYSDATE - 2 / 1440, 'YYYYMMDDHH24MISS') 
                     THEN CMNC_STTS_CD ELSE 'CMS2' END) COMM, 
               'CDS2' DOOR 
          FROM TB_WEB_CMRA_STTS A, 
               TB_WEB_CMRA      B, 
               TB_FCLT_INFR     C 
         WHERE A.WEB_CMRA_NMBR = B.WEB_CMRA_NMBR 
           AND B.WEB_CMRA_ID = C.FCLT_ID 
           AND NVL(C.DEL_YN, 'N') <> 'Y' 
        UNION 
        SELECT VMS_CTLR_ID ID, RGST_DT UPDT_DT, 
               (CASE WHEN RGST_DT > TO_CHAR(SYSDATE - 2 / 1440, 'YYYYMMDDHH24MISS') 
                     THEN CONN_STTS_CD ELSE 'CMS2' END) COMM, 
               (CASE WHEN RGST_DT > TO_CHAR(SYSDATE - 2 / 1440, 'YYYYMMDDHH24MISS') 
                     THEN CBOXDOOR_OPEN_STTS_CD ELSE 'CDS2' END) DOOR 
          FROM TB_VMS_STTS_PRST A, 
               TB_VMS_CTLR      B, 
               TB_FCLT_INFR     C 
         WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR 
           AND B.VMS_CTLR_ID = C.FCLT_ID 
           AND NVL( C.DEL_YN, 'N' ) <> 'Y' 
       ) 
 ORDER BY ID