December 19, 2005 at 9:30 pm
Hello all,
My project requires me to build a single query for making some multiple field calculations. Also tried to build a Query with the help of a member here 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 returns 3 values (Sum1, Sum2 Sum3) which are 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.
December 20, 2005 at 7:55 am
I am not to sure, but if the case does not work try three subqueries. that may work.
Select Sum1,Sum2,Sum3,
From
(
(SELECT sum(LDGR_BAL_AM) as [Sum1]
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN in (311000,312000) ) Sum1
(SELECT sum(LDGR_BAL_AM) as [Sum2]
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN in (911000,912000) ) Sum2
(SELECT sum(LDGR_BAL_AM) as [Sum3]
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN in (314000, 312000, 414000,454000) ) Sum3
) Ledger
Something like this might work
December 21, 2005 at 12:40 pm
Hello,
Thanks for your reply. Can you please give me the exact syntax for the above solution that you have proposed? especially,the from syntax for Sum1, Sum2, Sum3 ...
Thanks
December 21, 2005 at 12:54 pm
JKSQL DID give you the exact syntax. Pay attention, here it is again:
Select Sum1,Sum2,Sum3,
From
(
(SELECT sum(LDGR_BAL_AM) as [Sum1]
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN in (311000,312000) ) Sum1
(SELECT sum(LDGR_BAL_AM) as [Sum2]
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN in (911000,912000) ) Sum2
(SELECT sum(LDGR_BAL_AM) as [Sum3]
FROM DEVL8SGA.A_SGA_GRP_PSTN_SNP
WHERE ACCT_GRP_NO = 2100099
AND ACCT_NO = 791189
AND LDGR_NO IN in (314000, 312000, 414000,454000) ) Sum3
) Ledger
-SQLBill
December 21, 2005 at 12:55 pm
Also, we probably should add....this is a SQL Server site, not a DB2 site. The two SQL versions are different. JKSQL provided you the syntax for SQL Server.
-SQLBill
December 21, 2005 at 5:28 pm
Hello
Thanks for the reply. In the SQL statement given below,
select sum1,sum2,sum3
from (select........... as sum1
...................
..........................
) Ledger
Was wondering if the Query had any variables declared apart from just this query...cause in the select statement can sum1, sum2 be used before they are being assigned ?? And also the table name that we are accessing is DEVL8SGA.A.SGA...but you have mentioned 'Ledger'. What does this mean? Do we have to declare this or give the name of the actual table here. Please let me know about this.
I understand that this is in SQL and the syntax could be different in DB2. But since I can apply this same logic with no or very little manipulation I am counting on all your knowledge.
Thanks everyone for all the replies.
December 21, 2005 at 8:33 pm
It might help if you tell us which version of DB2 you are talking about - and on which platform: Windows/Linux, AS/400 or z/OS mainframe etc.
Also, what was the specific error you received?
December 21, 2005 at 9:01 pm
Looking more closely at your query *as posted* and the syntax is wrong. DB2 probably will not like the square brackets [], but you definitely need a comma separating your columns in the select:
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 )
December 22, 2005 at 11:48 am
The SUM1, SUM2, SUM3, and Ledger are table ALIASes.
-SQLBill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply