데이터 이관·검증 쿼리

SELECT
       o.id
  FROM old_cust o
  JOIN new_cust n ON n.id = o.id
 WHERE o.name IS DISTINCT FROM n.name
 ORDER BY o.id;

ID
--
4
9
(2행)

SELECT
       id
     , amt
  FROM old_cust
 WHERE NOT REGEXP_LIKE(amt, '^[0-9]+
  

)
 ORDER BY id;

ID | AMT
---+----
6  | N/A
(1행)

SELECT
       id
     , join_ymd
     , reason
  FROM (
        SELECT
               id
             , join_ymd
             , CASE
                 WHEN NOT REGEXP_LIKE(join_ymd, '^[0-9]{8}
  

) THEN '자리수'
                 WHEN CAST(SUBSTRING(join_ymd, 5, 2) AS INT) NOT BETWEEN 1 AND 12 THEN '월 범위'
                 WHEN CAST(SUBSTRING(join_ymd, 7, 2) AS INT)
                      NOT BETWEEN 1 AND DAY(LAST_DAY(CAST(SUBSTRING(join_ymd, 1, 4) || '-' || SUBSTRING(join_ymd, 5, 2) || '-01' AS DATE))) THEN '일 범위'
               END AS reason
          FROM old_cust
       ) t
 WHERE reason IS NOT NULL
 ORDER BY id;

ID | JOIN_YMD | REASON
---+----------+--------
5  | 20240231 | 일 범위
(1행)

SELECT
       id
     , name
     , LENGTH(name) AS len
  FROM old_cust
 WHERE LENGTH(name) > 6
 ORDER BY id;

ID | NAME             | LEN
---+------------------+----
12 | 조총무팀장대리님 | 8
(1행)

SELECT
       o.id
     , o.grade
  FROM old_cust o
 WHERE NOT EXISTS (SELECT 1 FROM grade_code g WHERE g.code = o.grade)
 ORDER BY o.id;

ID | GRADE
---+------
7  | G9
(1행)

SELECT
       err
     , COUNT(*) AS cnt
  FROM (
        SELECT
               o.id
             , CASE
                 WHEN NOT REGEXP_LIKE(o.amt, '^[0-9]+
  

) THEN '금액 변환 불가'
                 WHEN NOT REGEXP_LIKE(o.join_ymd, '^[0-9]{8}
  

) THEN '날짜 자리수'
                 WHEN CAST(SUBSTRING(o.join_ymd, 7, 2) AS INT)
                      > DAY(LAST_DAY(CAST(SUBSTRING(o.join_ymd, 1, 4) || '-' || SUBSTRING(o.join_ymd, 5, 2) || '-01' AS DATE))) THEN '날짜 일 범위'
                 WHEN LENGTH(o.name) > 6 THEN '이름 길이 초과'
                 WHEN NOT EXISTS (SELECT 1 FROM grade_code g WHERE g.code = o.grade) THEN '코드 매핑 누락'
                 ELSE '정상'
               END AS err
          FROM old_cust o
       ) t
 GROUP BY err
 ORDER BY cnt DESC, err;

ERR            | CNT
---------------+----
정상           | 8
금액 변환 불가 | 1
날짜 일 범위   | 1
이름 길이 초과 | 1
코드 매핑 누락 | 1
(5행)

-- MSSQL (2012 부터)
SELECT
       id
     , amt
     , join_ymd
  FROM old_cust
 WHERE TRY_CAST(amt AS INT) IS NULL
    OR TRY_CONVERT(DATE, join_ymd, 112) IS NULL;

-- Oracle (12c 릴리스 2 부터)
SELECT
       id
     , amt
     , join_ymd
  FROM old_cust
 WHERE VALIDATE_CONVERSION(amt AS NUMBER) = 0
    OR VALIDATE_CONVERSION(join_ymd AS DATE, 'YYYYMMDD') = 0;

-- MySQL
SELECT
       id
     , amt
     , join_ymd
  FROM old_cust
 WHERE amt NOT REGEXP '^[0-9]+
  


    OR STR_TO_DATE(join_ymd, '%Y%m%d') IS NULL;

id | amt | join_ymd
---+-----+---------
5  | 2000| 20240231
6  | N/A | 20240412

SET MODE Oracle;
SELECT id FROM old_cust
MINUS
SELECT id FROM new_cust
 ORDER BY id;

ID
--
5
6
7
12
(4행)