This is the query written by me, but the decode statement is not displaying the result in first row. Therefore,my other column data like INVI_FC_VAL is getting repeated for every ITED_TED_CODE.
Kindly refer the attachment for details.
SELECT
INVH_NO,INVH_DT,INVH_TXN_CODE,INVH_FORM_CODE,
INVI_ITEM_CODE,
FORM_DESC,
DECODE(ITED_TED_CODE,'BED','BED CT3',INVI_FC_VAL) BASIC_AMT,
NVL(DECODE(ITED_TED_CODE,'AED',ITED_NET_LC_AMT),0) AED,
NVL(DECODE(ITED_TED_CODE,'BED',ITED_NET_LC_AMT),0) BED,
NVL(DECODE(ITED_TED_CODE,'EDU CESS',ITED_NET_LC_AMT),0)EDU,
NVL(DECODE(ITED_TED_CODE,'SHECESS',ITED_NET_LC_AMT),0) SHE,
NVL(DECODE(ITED_TED_CODE,'BED CT3',ITED_NET_LC_AMT),0) BEDCT3,
NVL(DECODE(ITED_TED_CODE,'EDU CESS CT3',ITED_NET_LC_AMT),0) EDUCT3,
NVL(DECODE(ITED_TED_CODE,'SHECESS CT3',ITED_NET_LC_AMT),0) SHE_CT3,
SUM(NVL(DECODE(ITED_TED_CODE,
'CST 3%',ITED_NET_LC_AMT,
'CST 4%',ITED_NET_LC_AMT,
'VAT 4%',ITED_NET_LC_AMT,
'VAT 12.5%',ITED_NET_LC_AMT),0)) TAX,
SUM(NVL(DECODE(ITED_TED_CODE,
'DISC.',ITED_NET_LC_AMT,
'S - OCTROI',ITED_NET_LC_AMT,
'S-FREIGHT',ITED_NET_LC_AMT,
'S-INS',ITED_NET_LC_AMT,
'R-OFF',ITED_NET_LC_AMT,
'SROFF',ITED_NET_LC_AMT),0))OTHER
FROM
OT_INVOICE_HEAD,
OT_INVOICE_ITEM,
OT_INVOICE_ITEM_TED,
OM_FORM
WHERE INVH_SYS_ID = INVI_INVH_SYS_ID
AND INVH_SYS_ID = ITED_H_SYS_ID
--AND INVI_SYS_ID = ITED_I_SYS_ID
AND FORM_CODE = INVH_FORM_CODE
GROUP BY INVH_NO,INVH_DT,INVH_TXN_CODE,INVH_FORM_CODE,INVI_QTY,INVI_ITEM_CODE,FORM_DESC,INVI_FC_VAL,ITED_TED_CODE,
ITED_NET_LC_AMT
ORDER BY INVH_NO
-
Attachment: decode.JPG
(Size: 121.73KB, Downloaded 386 times)
|