Home » SQL & PL/SQL » SQL & PL/SQL » Display Result at specific or first row (Oracle 10g)
Display Result at specific or first row [message #332044] Mon, 07 July 2008 05:54 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
Given the table as follows:

ITED_TED_CODE ITED_NET_LC_AMT
AED 0
BED 42063
EDU CESS 841
SHECESS 421
BED CT3 6825
EDU CESS CT3 137
SHECESS CT3 68

The decode statements are as follows.
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,


When executed for the above data gives the output as follows :
AED BED EDU_CESS SHECESS BED_CT3 
0 
   42063 
          841 
                 421 
                         1400 

Whereas i want the output in first row in each column, as follows
AED  BED   EDU_CESS  SHECESS  BED_CT3
0   42063  841        421      1400 

i.e in same row.

Yogesh

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Mon, 07 July 2008 08:38] by Moderator

Report message to a moderator

Re: Display Result at specific or first row [message #332047 is a reply to message #332044] Mon, 07 July 2008 05:59 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please format your post properly next time.

Search this forum for matrix query,report you will get a number of posts for this problem.

Regards,
Rajat

Re: Display Result at specific or first row [message #332121 is a reply to message #332044] Mon, 07 July 2008 10:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Please post a reproducable example. You know, the code for create tables, inserts, and select query.

Kevin
Re: Display Result at specific or first row [message #332124 is a reply to message #332044] Mon, 07 July 2008 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Display Result at specific or first row

Rows within a table in any RDBMS have NO specific order!

Rows in a table are like balls in a basket.
Which red ball is the first one?

The returned rows are not necessarily returned in the order they are inserted.

[Updated on: Mon, 07 July 2008 10:11] by Moderator

Report message to a moderator

Re: Display Result at specific or first row [message #332163 is a reply to message #332044] Mon, 07 July 2008 13:40 Go to previous message
prtz
Messages: 11
Registered: January 2008
Junior Member
test@ora>
test@ora>
test@ora> --
test@ora> with t as (
  2    select 'AED' as ited_ted_code, 0 as ited_net_lc_amt from dual union all
  3    select 'BED',         42063 from dual union all
  4    select 'EDU CESS',      841 from dual union all
  5    select 'SHECESS',       421 from dual union all
  6    select 'BED CT3',      6825 from dual union all
  7    select 'EDU CESS CT3',  137 from dual union all
  8    select 'SHECESS CT3',    68 from dual)
  9  --
 10  select
 11    MAX(NVL(DECODE(ITED_TED_CODE,'AED',ITED_NET_LC_AMT),0))          AED,
 12    MAX(NVL(DECODE(ITED_TED_CODE,'BED',ITED_NET_LC_AMT),0))          BED,
 13    MAX(NVL(DECODE(ITED_TED_CODE,'EDU CESS',ITED_NET_LC_AMT),0))     EDU,
 14    MAX(NVL(DECODE(ITED_TED_CODE,'SHECESS',ITED_NET_LC_AMT),0))      SHE,
 15    MAX(NVL(DECODE(ITED_TED_CODE,'BED CT3',ITED_NET_LC_AMT),0))      BEDCT3,
 16    MAX(NVL(DECODE(ITED_TED_CODE,'EDU CESS CT3',ITED_NET_LC_AMT),0)) EDUCT3,
 17    MAX(NVL(DECODE(ITED_TED_CODE,'SHECESS CT3',ITED_NET_LC_AMT),0))  SHE_CT3
 18  from t;

       AED        BED        EDU        SHE     BEDCT3     EDUCT3    SHE_CT3
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0      42063        841        421       6825        137         68

1 row selected.

test@ora>
test@ora>
test@ora>
test@ora>



prtz
Previous Topic: Case Criteria
Next Topic: Dynamic SQL and Unicode
Goto Forum:
  


Current Time: Wed Dec 07 04:58:14 CST 2016

Total time taken to generate the page: 0.18924 seconds