Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: new to db2 - SUM of a column

    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??

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Can you explain why you can't use GROUP BY in DB2 iSeries?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    I get this error when I add "group by" to that DB2 Update statement:

    SQL State: 21000
    Vendor Code: -811
    Message: [SQL0811] Result of SELECT more than one row. Cause . . . . . : The result table of a SELECT INTO statement, a subquery, or a subselect of a SET statement contains more than one row. The error type is 2. If the error type is 1 then a SELECT INTO statement attempted to return more than one row. If the error type is 2 then a subselect of a basic predicate has produced more than one row. Only one row is allowed. Recovery . . . : Change the selection so that only one result row is returned and then try the request again. The DECLARE CURSOR, OPEN, and FETCH statements must be used to process more than one result row. For a subquery the IN, EXISTS, ANY or ALL predicates can be used to process more than one result row. If one row was expected, there may be data errors, such as duplicate rows, that are causing more than one row to be returned.



    Chapter 9 of this link mentions it , not sure what else to add to the where...the 2 tables match by p1yr, p1mn, p2de and I have those...

    http://www.centerfieldtechnology.com...ber%202006.pdf

    I guess if there's no other way, I can dump the "select....group by" results in a temp table , then update the PLAC...

  4. #4
    Join Date
    Mar 2004
    Posts
    8
    And anyone knows how to change that SQL Server version to DB2 version..DB2 doesnt recognize the derived table "MAIN"..but any other way to do it?

  5. #5
    Join Date
    Mar 2004
    Posts
    8

    Red face

    no one knows, come one ?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess noone wants to reverse-engineer your SQL to figure out what you are trying to do. If you explain your requirements in human language I'm sure somebody will be able to help.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Mar 2004
    Posts
    8
    I got it working. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •