i have a large query and one of my columns (DUTY_COST) should not be returning 0.00 values, however, what ever i try, i cannot exclude the value from the records.
Due to the number of columns the Actual/Expected results below I have limited to the last three columns but the full query is below.
Appreciate any help.
Actual Result
| DUTY_COST | ARAT | CSNO |
|---|---|---|
| 0 | 1.2635 | 6202.93.00.10 |
| 641.03 | 1.2635 | 6202.93.00.10 |
Expected Result
| DUTY_COST | ARAT | CSNO |
|---|---|---|
| 641.03 | 1.2635 | 6202.93.00.10 |
Expected Result to display IV.CEID in individual columns using Case When
| DUTY_COST | DUTY_KG_COST | FREIGHT_COST | BROKERAGE_COST | ARAT |
|---|---|---|---|---|
| 641.03 | 0 | 178.72 | 2.16 | 1.2635 |
My query:
WITH CTE_FGLEDG AS
(
SELECT * FROM FGLEDG
)
SELECT DISTINCT
F2.DIVI,
F2.FACI,
F2.WHLO,
MW.WHNM,
F2.SUNO,
ID.SUNM,
F2.PUNO,
F9.SINO,
F2.PNLI,
F2.PNLS,
MM.BUAR,
MM.PRGP,
MM.HDPR,
F2.ITNO,
MM.ITDS,
--MITLAD - MD.FUDS,
MH.TX15,
MH.TY15,
IA.DWDT,
IB.CODT,
IA.DUDT,
IB.ORQA,
IB.PUSL,
IB.PUST,
IB.OEND,
F9.ACQT,
IC.SUDO,
IB.PROJ,
IB.TEDL,
IB.MODL,
IA.CUCD,
IB.PUPR,
MT.TRPR,
CASE WHEN IV.CEID = 'DUTY' AND IV.CEVA > '0' THEN IV.CEVA ELSE 0 END AS DUTY_COST,
--CASE WHEN IV.CEID = 'DUTY/KG' THEN IV.CEVA ELSE 0 END AS DUTY_KG_COST,
--CASE WHEN IV.CEID = 'FRTIN' THEN IV.CEVA ELSE 0 END AS FREIGHT_COST,
--CASE WHEN IV.CEID = 'BRKG' THEN IV.CEVA ELSE 0 END AS BROKERAGE_COST,
--CASE WHEN IV.CEID = 'DUTY' THEN IV.WSOP ELSE 0 END AS DUTY_RATE,
F9.ARAT,
M9.CSNO
FROM FGRECL F2
LEFT JOIN CIDMAS ID ON ID.SUNO = F2.SUNO
LEFT JOIN FPLEDG EP ON EP.SUNO = F2.SUNO
LEFT JOIN CTE_FGLEDG CTE ON EP.JRNO = CTE.JRNO
LEFT JOIN MITMAS MM ON F2.ITNO = MM.ITNO
--LEFT JOIN MITLAD MD ON MD.ITNO = MM.ITNO AND MM.LNCD = 'GB'
LEFT JOIN MITMAH MH ON F2.ITNO = MH.ITNO
LEFT JOIN MPHEAD IA ON F2.PUNO = IA.PUNO
LEFT JOIN MPLINE IB ON F2.PUNO = IB.PUNO AND F2.ITNO = IB.ITNO
LEFT JOIN MPLIND IC ON F2.PUNO = IC.PUNO AND IC.PUOS = '45'
LEFT JOIN MITFAC M9 ON F2.ITNO = M9.ITNO AND F2.FACI = M9.FACI
LEFT JOIN MITWHL MW ON F2.WHLO = MW.WHLO
LEFT JOIN MPOEXP IV ON F2.PUNO = IV.PUNO AND F2.PNLI = IV.PNLI AND F2.PNLS = IV.PNLS AND F2.CUCD = IV.CUCD
LEFT JOIN MITTRA MT ON MT.RIDN = F2.PUNO AND MT.RIDL = F2.PNLI AND MT.WHLO = F2.WHLO AND MT.ITNO = F2.ITNO
LEFT JOIN FGINAE F9 ON F9.CONO = F2.CONO AND F9.DIVI = F2.DIVI AND F9.SUNO = F2.SUNO AND F9.PUNO = F2.PUNO AND F9.PNLI = F2.PNLI
AND EP.CONO = CTE.CONO
AND EP.DIVI = CTE.DIVI
WHERE F2.CONO = '100'
AND F2.SUNO = '6319'
AND F2.PUNO = '5330001073'
AND F2.ITNO = 'P000003-0001S'
AND CTE.AIT6 <> ''
ORDER BY F2.PUNO, F2.PNLI, F2.PNLS