|
이력쿼리문인데..제가 봐도봐도 제자리만 맴돌고 있습니다..도통 어찌해야할지 모르겠습니다..ㅜㅡㅜ..
결과는 나름나오는데 쿼리길고 행(?)걸린다고 연락왔습니다.. 도와주세요..
WITH CODE_TABLE AS
(
SELECT '01' code, 'a' code_name, 'A' stat FROM DUAL
UNION ALL SELECT '02', 'b', 'A' stat FROM DUAL
)
, CODE_TABLE1 AS
(
SELECT '01' code, 'prdt1' code_name, 'A' stat FROM DUAL
UNION ALL SELECT '02', 'prdt2', 'A' stat FROM DUAL
UNION ALL SELECT '03', 'prdt3', 'A' stat FROM DUAL
)
, MASTER_TABLE AS
(
SELECT 1 a_no, 1 a_nod, '01' mcode1,'01' mcode2,'01' mcode3,'01' mcode4,'01' mcode5, '111' jumin, 'hong1' name1, 'admin' name2, 'test' file_name FROM DUAL
UNION ALL SELECT 1, 2, '02' mcode1,'02' mcode2,'02' mcode3,'02' mcode4,'02' mcode5, '222', 'hong2', 'admin2' , 'null' FROM DUAL
)
, DETAIL_TABLE AS
(
SELECT 1 a_no, 1 a_nod, 1 a_sn, '01' code, '1' desc1, 10 wt, 10 money, 10 qt FROM DUAL
UNION ALL SELECT 1, 1, 2, '02', '2', 20, 20, 20 FROM DUAL
UNION ALL SELECT 1, 1, 3, '02', '3', 30, 30, 30 FROM DUAL
UNION ALL SELECT 1, 1, 4, '03', '4', 40, 40, 40 FROM DUAL
UNION ALL SELECT 1, 2, 1, '01', '1', 10, 10, 10 FROM DUAL
UNION ALL SELECT 1, 2, 2, '02', '2', 20, 20, 20 FROM DUAL
UNION ALL SELECT 1, 2, 3, '02', '3', 30, 30, 30 FROM DUAL
UNION ALL SELECT 1, 2, 4, '03', '21', 40, 40, 40 FROM DUAL
)
SELECT na a_no, nb a_nod, nd a_sn, item, oc old_item, nc new_item
FROM(
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = oc) oc
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = nc) nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'mcode1' item
FROM ( SELECT a_no oa, a_nod ob, mcode1 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, mcode1 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = oc) oc
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = nc) nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, nvl(nb,'') nb,NVL(nc, ' ') nc,nd, 'mcode2' item
FROM ( SELECT a_no oa, a_nod ob, mcode2 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, mcode2 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = oc) oc
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = nc) nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,nvl(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'mcode3' item
FROM ( SELECT a_no oa, a_nod ob, mcode3 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, mcode3 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item , oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'jumin' item
FROM ( SELECT a_no oa, a_nod ob, jumin oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, jumin nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item, oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'name1' item
FROM ( SELECT a_no oa, a_nod ob, name1 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, name1 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item, oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'name2' item
FROM ( SELECT a_no oa, a_nod ob, name2 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, name2 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = oc) oc
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = nc) nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'mcode4' item
FROM ( SELECT a_no oa, a_nod ob, mcode4 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, mcode4 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item, oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'file_name' item
FROM ( SELECT a_no oa, a_nod ob, file_name oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, file_name nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, nd, item
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = oc) oc
,(SELECT '['||code||']'||code_name FROM CODE_TABLE WHERE stat='A' AND code = nc) nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM (
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'mcode5' item
FROM ( SELECT a_no oa, a_nod ob, mcode5 oc, 0 od FROM MASTER_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, mcode5 nc, 0 nd FROM MASTER_TABLE newTable WHERE a_no=1 AND a_nod=2) n
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item
,(SELECT '['||code||']'||code_name FROM CODE_TABLE1 WHERE code = to_number(trim(oc))) oc
,(SELECT '['||code||']'||code_name FROM CODE_TABLE1 WHERE code = to_number(trim(nc))) nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, nvl(nb,'') nb,NVL(nc, ' ') nc,nd, 'code' item
FROM ( SELECT a_no oa, a_nod ob, code oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, code nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od(+)=nd
)
WHERE oc<>nc
UNION
SELECT oa, ob, oc, od, NVL(na,oa) na, NVL(nb,ob) nb, nc, nd, item
FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'code' item
FROM ( SELECT a_no oa, a_nod ob, code oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, code nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od=nd(+)
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item, oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'desc1' item
FROM ( SELECT a_no oa, a_nod ob, desc1 oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, desc1 nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od(+)=nd
)
WHERE oc<>nc
UNION
SELECT oa, ob, oc, od, na, nb, nc, nd, item FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'desc1' item
FROM ( SELECT a_no oa, a_nod ob, desc1 oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, desc1 nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od=nd(+)
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item, oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, NVL(nb,'') nb,NVL(nc, ' ') nc,nd, 'wt' item
FROM ( SELECT a_no oa, a_nod ob, TO_CHAR(wt) oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, TO_CHAR(wt) nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od(+)=nd
)
WHERE oc<>nc
UNION
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,NVL(na,'') na, nvl(nb,'') nb,NVL(nc, ' ') nc,nd, 'wt' item
FROM ( SELECT a_no oa, a_nod ob, TO_CHAR(wt) oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, TO_CHAR(wt) nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od=nd(+)
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item, oc, nc
FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item
FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,na,nb,NVL(nc, ' ') nc,nd, 'money' item
FROM ( SELECT a_no oa, a_nod ob, TO_CHAR(money) oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, TO_CHAR(money) nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od(+)=nd
)
WHERE oc<>nc
UNION
SELECT oa, ob, oc, od, na, nb, nc, nd, item FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,na,nb,NVL(nc, ' ') nc,nd, 'money' item
FROM ( SELECT a_no oa, a_nod ob, TO_CHAR(money) oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, TO_CHAR(money) nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od=nd(+)
)
WHERE oc<>nc
)
UNION ALL
SELECT oa, ob, od, NVL(na,oa) na, NVL(nb,ob) nb, NVL(nd,od) nd, item, oc, nc FROM
(
SELECT oa, ob, oc, od, na, nb, nc, nd, item FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,na,nb,NVL(nc, ' ') nc,nd, 'qt' item
FROM ( SELECT a_no oa, a_nod ob, TO_CHAR(qt) oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, TO_CHAR(qt) nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od(+)=nd
)
WHERE oc<>nc
UNION
SELECT oa, ob, oc, od, na, nb, nc, nd, item FROM(
SELECT oa,ob,NVL(oc, ' ') oc,od,na,nb,NVL(nc, ' ') nc,nd, 'qt' item
FROM ( SELECT a_no oa, a_nod ob, TO_CHAR(qt) oc, a_sn od FROM DETAIL_TABLE oldTable WHERE a_no=1 AND a_nod=2-1) o,
( SELECT a_no na, a_nod nb, TO_CHAR(qt) nc, a_sn nd FROM DETAIL_TABLE newTable WHERE a_no=1 AND a_nod=2) n
WHERE od=nd(+)
)
WHERE oc<>nc
)
)
/* where nb!=1 이부분은 풀면 원하는대로 나오지 않습니다.*/
ORDER BY na,od,nd |
