DB2 Query for extracting multiple columns/rows

[SA2011]

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 (parallel to each other)

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

 

[blom0344]

SELECT 
ACCT_N0,"other objects",
MAX(CASE WHEN LDGR_NO = 800100 THEN LDGR_BAL_AM ELSE NULL END) AS TRADE1,
MAX(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)
GROUP BY ACCT_NO,"other objects"

Just a thought, the mind is not clear.
The "other objects" are the dimensions you would want to summarize to get final result..

Ties Blom
Information analyst

 

[MarcLodge]

I can't immediately think of an answer to this as you are trying to associate data rows in your output that are not associated on the table.

One avenue which I would investigate is OLAP functions which allow you to order data OVER etc.

There's a good section in Graeme Birchall's cookbook (which has recently moved BTW) page 89 onwards. The cookbook can be found here:

http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V82CK.PDF

 

[ddiamond]

What you are describing is fairly easy to do with sub-queries once you have defined how you want the columns to line up. In your parallel example, you have them lined up where trade1 = trade2. Is this really the case? In any case, I am going to assume you have a field called row_no which you can use to align them. There are other posts on this forum that will show you how to create a row_no column if you choose to go that route.

select a.trade1, b.trade2
from
(
  SELECT ROW_NO, LDGR_BAL_AM TRADE1
  FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
  WHERE ACCT_NO = 791189 AND LDGR_NO = 800100
) a,
(
  SELECT ROW_NO, LDGR_BAL_AM TRADE2
  FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
  WHERE ACCT_NO = 791189 AND LDGR_NO = 800200
) b
where a.row_no = b.row_no

For simplicity, I have used an inner join, but you'd probably want to use a left join or full outer join. Also, there is nothing magical about the field row_no. You can substitute any field you want in place of it.