Help with SQl Query - to retrieve data in multiple rows

Question

Post reply

Help with SQl Query - to retrieve data in multiple rows

sheetal abbi

SSC Rookie

Points: 45
More actions
December 28, 2005 at 12:34 pm

#74967

Hello all,

Please look at Query1, in which I would like to add more CASE statements.

Query1

SELECT CASE WHEN LDGR_NO = 800100 THEN LDGR_BAL_AM ELSE NULL END AS TRADE1 FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP WHERE ACCT_NO = 791189 AND LDGR_NO = 800100

(When I execute the above query there are multiple records/rows pulled up. The result of the query is displayed below.

TRADE1

-----------

2200.0000

21000.0000

2700.0000

2000.0000

38000.0000

11000.0000

126000.0000

28000.0000

137500.0000

32700.0000

40000.0000

etc

For the same query above, I would like to add multiple cases like

Query2

SELECT CASE WHEN LDGR_NO = 800100 THEN LDGR_BAL_AM ELSE NULL END AS TRADE1,

CASE WHEN LDGR_NO = 800200 THEN LDGR_BAL_AM ELSE NULL END AS TRADE2

FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP

WHERE ACCT_NO = 791189 AND LDGR_NO IN(800100,800200)

But the result that i am getting is

                     TRADE1                       TRADE2

--------------------------- ---------------------------

                  2200.0000                            -

                 21000.0000                            -

                  2700.0000                            -

                  2000.0000                            -

                 38000.0000                            -

                 11000.0000                            -

                126000.0000                            -

                 28000.0000                            -

                137500.0000                            -

                 32700.0000                            -

                 40000.0000                            -

                 21800.0000                            -

                430000.0000                            -

                400000.0000                            -

                       TRADE1                       TRADE2

---------------------------++---------------------------

                   12900.0000                            -

                    4100.0000                            -

                   24000.0000                            -

                            -                    2200.0000

                            -                   21000.0000

                            -                    2700.0000

                            -                    2000.0000

                            -                   38000.0000

                            -                   11000.0000

                            -                  126000.0000

                            -                   28000.0000

                            -                  137500.0000

                            -                   32700.0000

                            -                   40000.0000

                            -                   21800.0000

                            -                  430000.0000

The result that I would like to get is

                     TRADE1                       TRADE2

--------------------------- ---------------------------

                  2200.0000                           2200.0000

                 21000.0000                           21000.0000

                  2700.0000                           2700.0000

                  2000.0000                           2000.0000

Please let me know how i could change my query(Query2) so that i can use multiple cases in the Query statement and have all the columns displayed parallel to each other.

Thanks

sheetal

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

You can't do this with a case because that processes for each row. You'd need some way to "join" the two queries together. In other words, how do you get Trade1 = 2200 equal to Trade2 = 2200 to get them on the same row?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

sheetal abbi SSC Rookie Points: 45 More actions · Answer 2

sheetal abbi

SSC Rookie

Points: 45

December 28, 2005 at 1:15 pm

#611768

Hey Steve

Thanks for the reply. Can you please give an example by building a query cause i dont know how to do a join cause there is only 1 table....and I have multiple columns that i need to retrieve this for.

Can u pls help.

Thanks

Sheetal

Charles Otten Default port Points: 1475 More actions · Answer 3

You can join a table to itself, but you really want to join to versions of the table. I believe the following:

select * from (

(select LDGR_BAL_AM as TRADE1 FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP

WHERE ACCT_NO = 791189 AND LDGR_NO = 800100 ) TR1 Join (select LDGR_BAL_AM as TRADE2 ELSE NULL END AS TRADE1 FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP

WHERE ACCT_NO = 791189 AND LDGR_NO = 800200) TR2

ON TR1.TRADE1 = TR2.TRADE2

Not knowing the data structure I'm not entirely sure this is what you are after.

If the phone doesn't ring...It's me.

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 728159

December 28, 2005 at 4:17 pm

#611812

Charles has a good example.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

The Aethyr Dragon SSChasing Mays Points: 606 More actions · Answer 5

If you want a summary per ACCT_NO you could do something like this:

SELECT ACCT_NO as ACCOUNT_NUMBER,

TRADE1 = SUM(CASE

WHEN LDGR_NO = 800100 THEN LDGR_BAL_AM

ELSE 0

END),

TRADE2 = SUM(CASE

WHEN LDGR_NO = 800200 THEN LDGR_BAL_AM

ELSE 0

END)

FROM A_SGA_GRP_PSTN_SNP

WHERE ACCT_NO = 791189 AND

LDGR_NO IN(800100,800200)

GROUP BY ACCT_NO

Which give you:

ACCOUNT_NUMBER TRADE1 TRADE2

-------------------- --------------------- ---------------------

791189 1292900.0000 892900.0000

Cheers

The Aethyr Dragon

Cape Town
RSA