WITH TAB1
AS
( SELECT 'A' AS ITEM, 1 AS SEQ, 100 AS AMT, '19980301' AS YMD FROM DUAL
UNION ALL SELECT 'A' AS ITEM, 2 AS SEQ, 200 AS AMT, '19980305' AS YMD FROM DUAL
UNION ALL SELECT 'A' AS ITEM, 3 AS SEQ, 150 AS AMT, '19980301' AS YMD FROM DUAL
UNION ALL SELECT 'B' AS ITEM, 1 AS SEQ, 100 AS AMT, '19980302' AS YMD FROM DUAL
UNION ALL SELECT 'B' AS ITEM, 2 AS SEQ, 120 AS AMT, '19980305' AS YMD FROM DUAL
UNION ALL SELECT 'B' AS ITEM, 3 AS SEQ, 200 AS AMT, '19980312' AS YMD FROM DUAL
UNION ALL SELECT 'D' AS ITEM, 1 AS SEQ, 100 AS AMT, '19980303' AS YMD FROM DUAL
UNION ALL SELECT 'D' AS ITEM, 2 AS SEQ, 300 AS AMT, '19980307' AS YMD FROM DUAL
UNION ALL SELECT 'D' AS ITEM, 3 AS SEQ, 200 AS AMT, '19980311' AS YMD FROM DUAL
)
, TAB2
AS
( SELECT 'A' AS ITEM, 250 AS AMT, '199803' AS YM FROM DUAL
UNION ALL SELECT 'C' AS ITEM, 300 AS AMT, '199803' AS YM FROM DUAL
UNION ALL SELECT 'D' AS ITEM, 500 AS AMT, '199803' AS YM FROM DUAL
UNION ALL SELECT 'A' AS ITEM, 200 AS AMT, '199804' AS YM FROM DUAL
)
SELECT DECODE(T.SEQ, 0, T.ITEM, ' ') AS ITEM
, DECODE(T.SEQ, 0, 'TOT', T.SEQ) AS SEQ
, SUM(TAB1_AMT) AS TAB1_AMT
, SUM(TAB2_AMT) AS TAB2_AMT
, DECODE(T.SEQ, 0, SUM(NVL(TAB1_AMT, 0) - NVL(TAB2_AMT, 0))) AS GAP_AMT
FROM ( SELECT T1.ITEM AS ITEM
, DECODE(D.NO, 1, T1.SEQ, 2, 0) AS SEQ
, SUM(T1.AMT) AS TAB1_AMT
, NULL AS TAB2_AMT
FROM TAB1 T1
JOIN (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2) D ON 1 = 1
WHERE T1.YMD > '199803'
GROUP BY T1.ITEM, DECODE(D.NO, 1, T1.SEQ, 2, 0)
UNION ALL
SELECT T2.ITEM AS ITEM
, 0 AS SEQ
, NULL AS TAB1_AMT
, T2.AMT AS TAB2_AMT
FROM TAB2 T2
WHERE T2.YM = '199803') T
GROUP BY T.ITEM, T.SEQ
ORDER BY T.ITEM, T.SEQ;
ITEM SEQ TAB1_AMT TAB2_AMT GAP_AMT
-------------------------------- ---------------------------------------- ---------- ---------- ----------
A TOT 450 250 200
1 100
2 200
3 150
B TOT 420 420
1 100
2 120
3 200
C TOT 300 -300
D TOT 600 500 100
1 100
2 300
3 200
