"데이터 품질 쿼리"의 두 판 사이의 차이

데이터 품질 체크(프로파일링 문장) 유효성 체크 SELECT M.DOC_SE_CODE

  FROM (SELECT DOC_SE_CODE
          FROM SPHSUSR.ATCHMNFL
         WHERE 1 = 1
           AND DOC_SE_CODE IS NOT NULL
           AND DOC_SE_CODE IS NOT NULL
         ) M
       LEFT OUTER JOIN SPHSUSR.DETAIL_CODE  CD
         ON M.DOC_SE_CODE = CD.DETAIL_CODE
        AND CD.GROUP_CODE = '1063'
 WHERE CD.DETAIL_CODE IS NULL

날짜 체크 SELECT COUNT(CREATE_DE) AS ANA_CNT

  FROM M.GVMAGNC_STD_CODE
 WHERE 1 = 1
   AND CREATE_DE IS NOT NULL
   AND (CASE WHEN SUBSTR(CREATE_DE, 1,1) BETWEEN '0' AND '9' 
                    AND SUBSTR(CREATE_DE, 2,1) BETWEEN '0' AND '9' 
                    AND SUBSTR(CREATE_DE, 3,1) BETWEEN '0' AND '9' 
                    AND SUBSTR(CREATE_DE, 4,1) BETWEEN '0' AND '9' 
                    AND SUBSTR(CREATE_DE, 5,1) BETWEEN '0' AND '9' 
                    AND SUBSTR(CREATE_DE, 6,1) BETWEEN '0' AND '9' 
  THEN CASE WHEN SUBSTR(CREATE_DE,5,2) IN ('01','02','03','04','05','06','07','08','09','10','11','12') 
                            THEN 1
                            ELSE 0
                        END
  ELSE 0 

  END != 1

- 데이터 패턴 체크 SELECT COUNT(*) AS ERR_CNT

  FROM (SELECT DATA_TYPE
              ,TRANSLATE(TRAN_DATA1,TRAN_DATA2, LPAD('C',TRAN_DATA2_LENGTH, 'C')) AS PATTERN
          FROM (SELECT DATA_TYPE
                       ,TRAN_DATA1
                       ,NVL(REPLACE(TRANSLATE(TRAN_DATA1,'9Aa~`!@#$%^&*()_-+=|{}[]:;?/<>,.|""B', LPAD(' ', 36,' ')), ' ', ),'C') AS TRAN_DATA2
                       ,LENGTH(NVL(REPLACE(TRANSLATE(TRAN_DATA1,'9Aa~`!@#$%^&*()_-+=|{}[]:;?/<>,.|""B', LPAD(' ', 36,' ')), ' ', ),'C')) AS TRAN_DATA2_LENGTH
                  FROM (SELECT DATA_TYPE
                              ,TRANSLATE(DATA_TYPE,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ~`!@#$%^&*()_-+=|{}[]:;?/<>,.|"" '
                                                   ,'9999999999aaaaaaaaaaaaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAA~`!@#$%^&*()_-+=|{}[]:;?/<>,.|""B') AS TRAN_DATA1
                          FROM M.USER_PH
                         WHERE 1 = 1
                            AND DATA_TYPE IS NOT NULL

                        )
                )
        )

)