DB2 MULTIPLE CALCULATIONS - SINGLE QUERY HELP !!!

[SA2011]

Hello all,

My project requires me to build a single query for making some multiple field calculations. Also tried to build a Query which is as below

SELECT

sum(case when LDGR_NO in (311000,312000) then LDGR_BAL_AM else 0 end) as Sum1
sum(case when LDGR_NO in (911000,912000) then LDGR_BAL_AM else 0 end) as Sum2
sum(case when LDGR_NO in (314000, 312000, 414000,454000) then LDGR_BAL_AM else 0 end) as Sum3
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN (311000, 312000,911000, 912000,314000, 312000, 414000,454000 )

(The above query SHOULD return 3 values (Sum1, Sum2 Sum3) which are GOT by adding up the ldgr_bal_am for 3 sets of ledger numbers found.

Tried to execute this in Db2, but it didnt work. Showed an error in 'case when LDGR_NO in (311000,312000) ....' Can anyone help me in this query and also suggest if there is any other way to Query this in Db2?.

Thanks.

 

[blom0344]

Select
sum(case when LDGR_NO in (311000,312000) then LDGR_BAL_AM else 0 end) as Sum1[b],[/b]
 sum(case when LDGR_NO in (911000,912000) then LDGR_BAL_AM else 0 end) as Sum2[b],[/b] 
 sum(case when LDGR_NO in (314000, 312000, 414000,454000) then LDGR_BAL_AM else 0 end) as Sum3 
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP 
WHERE  ACCT_GRP_NO                    = 2100099 
 AND  ACCT_NO                        = 791189 
 AND  LDGR_NO IN (311000, 312000,911000, 912000,314000, 312000, 414000,454000 )

You forgot 2 comma's.
Check also that LDGR_NO is a numeric field..


Ties Blom
Information analyst

 

[SA2011]

Hello Ties

Thanks for replying to my Query.

Q1
===
Select
sum(case when LDGR_NO in (311000,312000) then LDGR_BAL_AM else 0 end) as Sum1,
sum(case when LDGR_NO in (911000,912000) then LDGR_BAL_AM else 0 end) as Sum2,
sum(case when LDGR_NO in (314000, 312000, 414000,454000) then LDGR_BAL_AM else 0 end) as Sum3
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN (311000, 312000,911000, 912000,314000, 312000, 414000,454000 )


I tried to edit the above Query - put the commmas and execute but its giving me the following error.

QUERY MESSAGES:
The search-condition in a SEARCH-WHEN-CLAUSE is not valid. (My Guess is that its not taking the IN predicate)

The Ldgr_No field is Numeric and a simple Query like below works fine...

Q2 :

SELECT
CASE WHEN LDGR_NO = 311000 THEN LDGR_BAL_AM ELSE 0 END AS TEST1,
CASE WHEN LDGR_NO = 800100 THEN LDGR_BAL_AM ELSE 0 END AS TEST2,
CASE WHEN LDGR_NO = 800200 THEN LDGR_BAL_AM ELSE 0 END AS TEST3
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_NO = 791189 AND AST_NO = '237305' AND
EFF_TS='2005-08-31-21.00.00.000000' AND LDGR_NO IN (311000,800100,800200)

Please advise if there is any other way or anything else i need to take care , to execute the Q1 Query.

Thanks

 

[MarcLodge]

SA2011,
On the surface of it I can see no reason why your case statement does not work. I've seen CASE statements that use LIKE 'W%' expressions, so would have thought that what you have coded would be fine. Clearly it is not, so we have to find another way. If it were mine, I would try:

Select sum(case when LDGR_NO = 311000 then LDGR_BAL_AM
                when LDGR_NO = 312000 then LDGR_BAL_AM
                else 0 
           end) as Sum1,
       sum(case when LDGR_NO = 911000 then LDGR_BAL_AM
                when LDGR_NO = 912000 then LDGR_BAL_AM
                else 0 
           end) as Sum2,
       sum(case when LDGR_NO = 314000 then LDGR_BAL_AM
                when LDGR_NO = 312000 then LDGR_BAL_AM
                when LDGR_NO = 414000 then LDGR_BAL_AM
                when LDGR_NO = 454000 then LDGR_BAL_AM
                else 0 
           end) as Sum3
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP 
WHERE  ACCT_GRP_NO = 2100099 
  AND  ACCT_NO     = 791189 
  AND  LDGR_NO     IN (311000, 312000,911000, 912000,314000, 312000, 414000,454000 )

If that didn't work, I'd be inclined to look at changing the CASE statement further using a slightly different format.
If efficiency allowed, I might even consider some sort of UNION, getting each set of data in three separate but UNIONed queries.

Let us know if any of this helps.

Regards,

Marc

 

[SA2011]

Hello Marc

This did work. Thanks a heap.

Also, got to know that we cant use the IN Predicate in the select statement, so i guess thats where i was going wrong.

Thanks for your advise and help and also want to find out what you meant by having more efficiency.Did you mean that by using Union it would be more efficient. Can you please give me an example as to how I could use it in my scenario?

Thanks
Sheetal

 

[MarcLodge]

Sheetal,
I obviously didn't make myself clear on the UNION bit. What I meant was that if the tables were relatively small and therefore efficiency was not an issue, you could run three separate queries and union them together. Thinking about it, this approach would bring you back three separate rows, which is probably not want you require.

Pleased to hear that the problem has been solved.


Marc