zeroifnull teradata

I have this syntax and it appears everything is pulling correctly. However, when there are months with no demand it does not return any value. I've tried adding zeroifnull or a coalesce on the sum(dem_qty) but I am still not getting the correct amount of records. Any idea how to fix that?


SELECT

product, Stocking, CAST(CAST(ord_date AS FORMAT 'YYYYMM') AS VARCHAR(6)) as cal_yr_mo, SUM(DEM_QTY) as dmd_qty
from PRD_DWH_VIEW_LMT.Demand_History_Agg_V
where fcstable = 'x'
and stocking = '005'

AND cal_yr_mo <
(sel Max(cal_yr_mo) from PRD_DWH_VIEW_LMT.SPP_9ADEMAND_view
where cal_yr_mo not in (sel Max(cal_yr_mo) from PRD_DWH_VIEW_LMT.SPP_9ADEMAND_view)
)

group by 1,2,3

UNION ALL

Select product, STKHLDNG_LOC, cal_yr_mo, ZEROIFNULL(sum(Final_DMD_QTY)) as dmd_qty

From (

Select
a.PRODUCT, a.STKHLDNG_LOC, a.FACING_LOC, CAST(a.cal_yr_mo AS VARCHAR(6)) as cal_yr_mo,
a.DMD_QTY, a.DMD_CNT, a.DEMAND_CAT, a.batch_ts,
(b.DMD_QTY) as CRT_DMD_QTY, (b. DMD_CNT) as CRT_DMD_CNT, (b.DEMAND_CAT) as CRT_DEMAND_CAT,
(a.DMD_QTY + ZEROIFNULL (b.DMD_QTY)) as Final_DMD_QTY,
(a.DMD_CNT + ZEROIFNULL (b. DMD_CNT)) as Final_DMD_CNT
FROM PRD_DWH_VIEW_LMT.SPP_9ADEMAND_view as a


LEFT JOIN

(SELECT PRODUCT, STKHLDNG_LOC, CAL_YR_WK as CAL_YR_WK_B, CAL_YR_MO as CAL_YR_MO_B,
DMD_QTY, DMD_CNT, DEMAND_CAT
FROM PRD_DWH_VIEW_LMT.SPP_9ADEMCRT_view where STCKHLD_LOC_VCL = 'X' AND demand_cat <> 'NONE'
) as b


ON a.STKHLDNG_LOC = b.STKHLDNG_LOC
and a.CAL_YR_WK = CAL_YR_WK_B
and a.CAL_YR_MO = CAL_YR_MO_B
and a.PRODUCT = b.PRODUCT
and a.DEMAND_CAT = b.DEMAND_CAT
WHERE a.stkhldng_loc = '005'
AND a.STCKHLD_LOC_VCL = 'X'
AND a.demand_cat <> 'NONE'
AND cal_yr_mo in


(sel Max(cal_yr_mo) from PRD_DWH_VIEW_LMT.SPP_9ADEMAND_view
where cal_yr_mo not in (sel Max(cal_yr_mo) from PRD_DWH_VIEW_LMT.SPP_9ADEMAND_view)
)


) as a group by 1,2,3 ```

Continue reading...