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

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  December 28, 2005 at 1:06 pm

  #611763

  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?

• sheetal abbi

  SSC Rookie

  Points: 45

  More actions

  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

  December 28, 2005 at 1:23 pm

  #611771

  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: 736212

  More actions

  December 28, 2005 at 4:17 pm

  #611812

  Charles has a good example.

• The Aethyr Dragon

  SSChasing Mays

  Points: 636

  More actions

  December 29, 2005 at 1:29 am

  #611856

  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