Home » SQL & PL/SQL » SQL & PL/SQL » Decode to display result of the columns in one row
Decode to display result of the columns in one row [message #330841] Tue, 01 July 2008 08:21 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
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 89 times)
Re: How to use Decode to display result in first row [message #330845 is a reply to message #330841] Tue, 01 July 2008 08:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

A table is like a basket of colored balls.

>How to use Decode to display result in first row
Which red ball is the "first" ball in the basket?
Re: How to use Decode to display result in first row [message #330847 is a reply to message #330841] Tue, 01 July 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.
But before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Decode to display result of the columns in one row [message #330857 is a reply to message #330841] Tue, 01 July 2008 08:48 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
please refer the attachment for details.

Yogesh
  • Attachment: decode.JPG
    (Size: 121.73KB, Downloaded 85 times)
Re: Decode to display result of the columns in one row [message #330859 is a reply to message #330841] Tue, 01 July 2008 08:52 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines below
http://www.orafaq.com/forum/t/88153/0/

do NOT open new threads for same problem!
Previous Topic: Index
Next Topic: Problem: Passing parameter
Goto Forum:
  


Current Time: Sun Dec 04 16:54:50 CST 2016

Total time taken to generate the page: 0.04302 seconds