Hi,

Is there a way to join the two tables ITMMVT and ITMCOST such that values of ITMREF,STOFCY from ITMMVT display when these are NULL for ITMCOST and values of ITMREF and STOFCY for ITMCOST display when these are NULL for ITMMVT.

I can get the first case with the following query, but cannot seem to capture any Average costs for ITMCOST records (if no STD cost exists).

select C.STOFCY_0, I.TCLCOD_0, C.ITMREF_0, I.ITMDES1_0, C.ITCSEQ_0, I.STU_0,C.VLTTOT_0, M.AVC_0, M.LASRCPPRI_0, M.PHYSTO_0, M.CTLSTO_0, M.REJSTO_0, ((M.PHYSTO_0 * M.AVC_0) + (M.AVC_0 * M.CTLSTO_0) + (M.REJSTO_0 * M.AVC_0)), ((C.VLTTOT_0 * M.PHYSTO_0)+(C.VLTTOT_0 * M.CTLSTO_0) + (M.REJSTO_0 * C.VLTTOT_0)) from CICPROD.ITMCOST C
FULL OUTER JOIN CICPROD.ITMMASTER I ON C.ITMREF_0=I.ITMREF_0
FULL OUTER JOIN CICPROD.ITMMVT M ON M.ITMREF_0 = C.ITMREF_0 AND M.STOFCY_0 =C.STOFCY_0
where ((C.STOFCY_0 = CASE WHEN %1% = '' THEN C.STOFCY_0 ELSE %1% END) OR C.STOFCY_0 IS NULL)

AND C.ITCSEQ_0 = (SELECT MAX(ITCSEQ_0) FROM CICPROD.ITMCOST C1 WHERE C1.ITMREF_0=C.ITMREF_0

AND C1.STOFCY_0 =C.STOFCY_0 ) AND ((C.ITMREF_0 = CASE WHEN %2% = '' THEN C.ITMREF_0 ELSE %2% END) OR C.ITMREF_0 IS NULL)

AND ((I.TCLCOD_0 = CASE WHEN %3% = '' THEN I.TCLCOD_0 ELSE %3% END) OR I.TCLCOD_0 IS NULL)

GROUP BY C.STOFCY_0, I.TCLCOD_0, C.ITMREF_0,I.ITMDES1_0, I.STU_0,C.ITCSEQ_0,C.VLTTOT_0, M.AVC_0, M.LASRCPPRI_0, M.PHYSTO_0, M.CTLSTO_0, M.REJSTO_0, ((M.PHYSTO_0 * M.AVC_0) + (M.AVC_0 * M.CTLSTO_0) + (M.REJSTO_0 * M.AVC_0))

ORDER BY C.ITMREF_0 ASC, C.STOFCY_0 ASC

Thank you!