데이터 연동 테이블인데 변동이 있는 값만 추출하고싶습니다 minus 이용
새로운 데이터 ( 키 , 벨류 )
a 2
b 1
c 2
e 1
기존 데이터 ( 키 , 벨류 )
a 1
b 1
c 1
d 1
추출하고싶은 데이터 ( 키 , 벨류 , 상태 )
a 2 u(업데이트)
c 2 u(업데이트)
d 1 d(딜리트)
e 1 i(인서트)
위처럼 새로운 데이터 minus 기존데이터 하면
추출하고싶은 데이터가 나올꺼같은데..
삭제된 d 1 은 추출이 안되네요
그리고 상태도 뽑을수 없고요
쿼리로 만들어보면
WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)
SELECT * FROM NEW_DATA
MINUS
SELECT * FROM TA_DATA
결과
key val
a 2
c 2
e 1
입니다
원하는 결과는
key val status
a 2 u
c 2 u
d 1 d
e 1 i
이렇게 뽑고싶은게 이거 가능한건가요 ㅠㅠ
도움 부탁드립니다..
WITH TA_DATA AS ( SELECT 'a' key , '1' val from dual UNION ALL SELECT 'b' key , '1' val from dual UNION ALL SELECT 'c' key , '1' val from dual UNION ALL SELECT 'd' key , '1' val from dual ) , NEW_DATA AS ( SELECT 'a' key , '2' val from dual UNION ALL SELECT 'b' key , '1' val from dual UNION ALL SELECT 'c' key , '2' val from dual UNION ALL SELECT 'e' key , '1' val from dual ) SELECT NVL(A.KEY,B.KEY) KEY , NVL(A.VAL,B.VAL) VAL , CASE WHEN B.KEY IS NULL THEN 'I' WHEN A.KEY IS NULL THEN 'D' WHEN A.VAL != B.VAL THEN 'U' END STATUS FROM NEW_DATA A FULL OUTER JOIN TA_DATA B ON A.KEY = B.KEY WHERE CASE WHEN B.KEY IS NULL THEN 'I' WHEN A.KEY IS NULL THEN 'D' WHEN A.VAL != B.VAL THEN 'U' END IS NOT NULL ORDER BY 1
WITH TA_DATA AS ( SELECT 'a' key , '1' val from dual UNION ALL SELECT 'b' key , '1' val from dual UNION ALL SELECT 'c' key , '1' val from dual UNION ALL SELECT 'd' key , '1' val from dual ) , NEW_DATA AS ( SELECT 'a' key , '2' val from dual UNION ALL SELECT 'b' key , '1' val from dual UNION ALL SELECT 'c' key , '2' val from dual UNION ALL SELECT 'e' key , '1' val from dual ) SELECT TD.KEY, TD.VAL, ND.KEY N_KEY, ND.VAL N_VAL, CASE WHEN TD.VAL = ND.VAL THEN 'EQUALS' WHEN TD.VAL IS NULL AND ND.VAL IS NOT NULL THEN 'INSERT' WHEN TD.VAL IS NOT NULL AND ND.VAL IS NULL THEN 'DELETE' WHEN TD.VAL != ND.VAL THEN 'UPDATE' END STATUS FROM TA_DATA TD FULL OUTER JOIN NEW_DATA ND ON (TD.KEY = ND.KEY) ORDER BY NVL(TD.KEY, ND.KEY)
WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)
SELECT * FROM NEW_DATA
MINUS
SELECT * FROM TA_DATA
결과
key val
a 2
c 2
e 1
반대로
WITH TA_DATA AS (
SELECT 'a' key , '1' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '1' val from dual
UNION ALL
SELECT 'd' key , '1' val from dual
) , NEW_DATA AS (
SELECT 'a' key , '2' val from dual
UNION ALL
SELECT 'b' key , '1' val from dual
UNION ALL
SELECT 'c' key , '2' val from dual
UNION ALL
SELECT 'e' key , '1' val from dual
)
SELECT key , '' ad val FROM TA_DATA
MINUS
SELECT key , '' ad val FROM NEW_DATA
결과
d ''
이런식으로 나온거 .. 유니온 올 하면
변경된 목록과 삭제ㄷ할 목록을 뽑을수 있을꺼같은데
어떨까요???
키는 하나 인건가요?
키만 비교하는 걸로 했습니다.
WITH TA_DATA AS ( SELECT 'a' key , '1' val from dual UNION ALL SELECT 'b' key , '1' val from dual UNION ALL SELECT 'c' key , '1' val from dual UNION ALL SELECT 'd' key , '1' val from dual ) , NEW_DATA AS ( SELECT 'a' key , '2' val from dual UNION ALL SELECT 'b' key , '1' val from dual UNION ALL SELECT 'c' key , '2' val from dual UNION ALL SELECT 'e' key , '1' val from dual ) SELECT BASIS.KEY BASIS_KEY, TD.KEY, TD.VAL, ND.KEY N_KEY, ND.VAL N_VAL, CASE WHEN TD.KEY IS NOT NULL AND ND.KEY IS NULL THEN 'DELETE' WHEN TD.KEY IS NULL AND ND.KEY IS NOT NULL THEN 'INSERT' WHEN TD.KEY IS NOT NULL AND ND.KEY IS NOT NULL THEN 'UPDATE' END STATUS FROM (SELECT DISTINCT KEY FROM ((SELECT * FROM NEW_DATA UNION ALL SELECT * FROM TA_DATA ) MINUS (SELECT * FROM NEW_DATA INTERSECT SELECT * FROM TA_DATA ) ) ) BASIS, TA_DATA TD, NEW_DATA ND WHERE BASIS.KEY = TD.KEY(+) AND BASIS.KEY = ND.KEY(+)
WITH ta_data AS ( SELECT 'a' key, 1 val1, 'a' val2, sysdate val3 FROM dual UNION ALL SELECT 'b', 1, 'a', sysdate FROM dual UNION ALL SELECT 'c', 1, 'a', sysdate FROM dual UNION ALL SELECT 'd', 1, 'a', sysdate FROM dual ) , new_data AS ( SELECT 'a' key, 2 val1, 'a' val2, sysdate val3 FROM dual UNION ALL SELECT 'b', 1, 'a', sysdate FROM dual UNION ALL SELECT 'c', 2, 'a', sysdate FROM dual UNION ALL SELECT 'e', 1, 'a', sysdate FROM dual ) SELECT a.* , NVL2(b.key, 'u', 'i') status FROM (SELECT * FROM new_data MINUS SELECT * FROM ta_data ) a , ta_data b WHERE a.key = b.key(+) UNION ALL SELECT b.* , 'd' status FROM new_data a , ta_data b WHERE a.key(+) = b.key AND a.key IS NULL ORDER BY 1 ;