SELECT ITE.ITEM_NBR, ITE.ITEM_DESCR, ACTUAL_TOT, ESTIMATE_TOT, CHECK_NBR
FROM (SELECT ITEM_NBR,
SUM(ACTUAL_AMT) ACTUAL_TOT,
(CASE
WHEN COUNT(DISTINCT CHECK_NBR) != 1 THEN
'Mixed'
ELSE
MAX(CHECK_NBR)
END) CHECK_NBR
FROM ACTUALS
GROUP BY ITEM_NBR) ACT,
(SELECT ITEM_NBR, SUM(ESTIMATED_AMT) ESTIMATE_TOT
FROM ESTIMATES
GROUP BY ITEM_NBR) EST,
ITEMS ITE
WHERE ITE.ITEM_NBR = EST.ITEM_NBR(+)
AND ITE.ITEM_NBR = ACT.ITEM_NBR(+)
ORDER BY ITEM_NBR
FROM (SELECT ITEM_NBR,
SUM(ACTUAL_AMT) ACTUAL_TOT,
(CASE
WHEN COUNT(DISTINCT CHECK_NBR) != 1 THEN
'Mixed'
ELSE
MAX(CHECK_NBR)
END) CHECK_NBR
FROM ACTUALS
GROUP BY ITEM_NBR) ACT,
(SELECT ITEM_NBR, SUM(ESTIMATED_AMT) ESTIMATE_TOT
FROM ESTIMATES
GROUP BY ITEM_NBR) EST,
ITEMS ITE
WHERE ITE.ITEM_NBR = EST.ITEM_NBR(+)
AND ITE.ITEM_NBR = ACT.ITEM_NBR(+)
ORDER BY ITEM_NBR
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24558043/viewspace-676928/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
