Been working on this all day! So hope someone could help me
I have a SQL that works fine in SQL Server. I want to do the same thing in AS400/DB2 but I cant get it right because I cant use "group by" in AS400...
I will post the SQL Server version and what I have for DB2:
This is the select in DB2, this returns 2 rows..one for year 2005, month 09..one for 2004, month 02
Code:
Select p1.p1mn,p1.p1yr,sum(t1.P2P) From Trans t1 , Plac p1
Where 1.P2c = p1.p1s
AND t1.p2rr =''
AND Year(t1.P2De)='2005'
AND p1.P1Yr=Year(t1.p2A)
AND p1.P1Mn=
(concat(CASE WHEN Month(t1.p2A) < 10 THEN '0' Else '' END),cast(Month(t1.p2A) as char(2)) )
and p1.p1s='AAN223'
Group By p1.p1mn,p1.p1yr
This is the same select, with no Group By (Group by gives an error), changed into an Update. This Update updates rows that are year 2003 as well! I think I need to narrow it down but not sure how. This updates 17 rows instead of 2 rows
Code:
Update Plac Set P1C = (Select sum(t1.P2P) From Trans t1 , Plac p1
Where 1.P2c = p1.p1s
AND t1.p2rr =''
AND Year(t1.P2De)='2005'
AND p1.P1Yr=Year(t1.p2A)
AND p1.P1Mn=
(concat(CASE WHEN Month(t1.p2A) < 10 THEN '0' Else '' END),cast(Month(t1.p2A) as char(2)) )
and p1.p1s='AAN223'
)
This is the SQL Server version that WORKS fine because I can use group by and derived table (MAIN) . This update, updates 2 correct rows
Code:
Update Plac set P1CL = Main.SumAmount
from Plac p,
inner join
( Select t1.p2c,p1.P1Yr,p1.P1Mn,sum(t1.P2P) as SumAmount
from Trans t1
inner join Plac p1
on p1.P1S=t1.P2C
where p1.P1Yr=Year(t2.p2A)
AND
P1Mn=(CASE WHEN Month(t1.p2A) < 10 THEN '0' Else '' END) + CONVERT( VARCHAR(2), Month(t1.p2A))
AND
t1.p2rs ='' AND Year(t1.P2De)='2005'
and t1.p2c='AAN33' -- for testing
group by p2c,p1yr,p1mn) AS Main
ON p.p1s = Main.P2c
and p.P1Yr=main.p1yr
AND Main.P1Mn=p.p1mn
Any ideas on how to make the DB2 one work??
dBforums
>
new to db2 - SUM of a column
12-13-06, 18:44
Linear Mode
