ITS_OP/SRC/SQL/시설물 장애이력 조회.sql

SELECT X.FCLT_TYPE,                                 
       X.FCLT_LCTN,                                 
       X.FCLT_ID,                                   
       X.FAIL_HS_SEQ,                               
       X.EQPM_TYPE_CD,                              
       (SELECT EQPM_TYPE_CD_NM                      
          FROM TB_FAIL_EQPM_TYPE                    
         WHERE EQPM_TYPE_CD = X.EQPM_TYPE_CD )      
       AS EQPM_TYPE_CD_NM,                          
       X.OCRR_DT,                                   
       X.RCPR_NM,                                   
       X.ADTN_DT,                                   
       X.ADTN_NM,                                   
       X.CHNG_NM,                                   
       X.CHNG_DT,                                   
       X.FAIL_OCRR_CD,
       (SELECT FAIL_OCRR_CD_NM
          FROM TB_FAIL_OCRR_DVSN
         WHERE FAIL_OCRR_CD = X.FAIL_OCRR_CD)
       AS FAIL_OCRR_CD_NM,                              
       X.FAIL_TYPE,
       (SELECT A.FAIL_CD_NM
          FROM TB_FAIL_TYPE    A,
               TB_FAIL_DETL_CD B
         WHERE B.EQPM_TYPE_CD = X.EQPM_TYPE_CD
           AND B.FAIL_OCRR_CD = X.FAIL_OCRR_CD
           AND B.FAIL_CD      = X.FAIL_TYPE
           AND A.FAIL_CD      = B.FAIL_CD )
       AS FAIL_CD_NM,                                 
       X.ETC_FAIL_CONT,                             
       NVL(Y.FAIL_HS_SEQ, 'X') AS RPAR_HS,          
       Y.WRKR_NM,                                   
       Y.WORK_STRT_DT,                              
       Y.WORK_END_DT,                               
       Y.WORK_FINS_YN,                              
       Y.FAIL_HNDL_TYPE,                            
       (SELECT FAIL_HNDL_CD_NM                      
          FROM TB_FAIL_HNDL_TYPE                    
         WHERE FAIL_HNDL_CD = Y.FAIL_HNDL_TYPE)     
       AS FAIL_HNDL_CD_NM,                          
       Y.ETC_HNDL_CONT,                             
       Y.ADTN_WRKR_NM,                              
       Y.ADTN_WORK_STRT_DT,                         
       Y.ADTN_WORK_END_DT,                          
       Y.DEL_YN                                     
  FROM (SELECT B.FCLT_TYPE, B.FCLT_LCTN, A.*        
          FROM TB_FCLT_FAIL_HS      A,              
               TB_FCLT_INFR         B               
         WHERE A.OCRR_DT BETWEEN '1' AND '9999999999999999999999'      
           AND A.FCLT_ID = B.FCLT_ID                
       ) X,                                         
       (SELECT *                                    
          FROM TB_FCLT_FAIL_RPAR_HS C               
         WHERE C.FAIL_HS_SEQ IN                     
               (SELECT FAIL_HS_SEQ                  
                  FROM TB_FCLT_FAIL_HS              
                 WHERE OCRR_DT                      
               BETWEEN '1' AND '9999999999999999999999')               
           AND C.DEL_YN <> 'Y'                      
       ) Y                                          
 WHERE X.FAIL_HS_SEQ = Y.FAIL_HS_SEQ(+)             
 ORDER BY X.FCLT_TYPE, X.OCRR_DT