- UNION queries perform poorly as they can scan through the same data multiple times.
- Require any logic changes to be made in multiple locations
SELECT
INVI.CODE,
INVI.NAME,
SUM (FORECAST_COST) FORECAST_COST,
SUM(BUDGET_COST) BUDGET_COST FROM
(SELECT INVI.CODE, INVI.NAME,
FP.TOTAL_COST,
FP.FORECAST_COST
0 BUDGET_COST FROM INV_INVESTMENTS INVI
JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND
INVI.OBJECT_CODE =FP.OBJECT_CODE
WHERE FP.IS_PLAN_OF_RECORD =1 AND FP.PLAN_TYPE_CODE="FORECAST"
UNION ALL
SELECT INVI.CODE, INVI.NAME,
0 FORECAST_COST,
FP.TOTAL_COST BUDGET_COST FROM INV_INVESTMENTS INVI
JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND
INVI.OBJECT_CODE =FP.OBJECT_CODE
WHERE FP.IS_PLAN_OF_RECORD =1 AND FP.PLAN_TYPE_CODE="BUDGET")
WHERE INVI.CODE ="PR0J01793"
GROUP BY CODE,NAME
* Most UNION queries can easily replaced with logic
SELECT
INVI.CODE,
INVI.NAME,
SUM(CASE WHEN FP.PLAN_TYPE_CODE="FORECAST" THEN FP.TOTAL_COST END) FORECAST_COST
SUM(CASE WHEN FP.PLAN_TYPE_CODE="BUDGET" THEN FP.TOTAL_COST END) BUDGET_COST
FROM INVI.INVESTMENTS
JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND INVI.ODF_OBJECT_CODE=FP.OBJECT_CODE
WHERE FP.IS_PLAN_OF_RECORD=1 AND INVI.CODE="PR0J01793"
GROUP BY INVI.CODE, INVI.NAME
