Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Need help to combine the SQL

    TABLE-A

    ACTY-CD INPUT-SRC-CD HDR-CD AMT
    2 5 1 890
    3 5 1 889
    12 5 2 750
    14 5 2 151
    15 5 2 321
    12 5 4 900
    14 5 4 300
    15 5 4 290

    TABLE-B
    ACTY-CD ACTY-TXT
    2 PUR
    3 RED
    12 CAP
    14 DIV
    15 REV

    Need some help to write one SQL for the 3 sQLs below. The difference in each SQL is the value on Hdr_CD = 4 ,based on acty_cd.

    select acty.ACTY_txt
    , sum(RPT.AMT) as amt
    FROM TABEL-A RPT
    JOIN TABLE-B ACTY
    ON ACTY.ACTY_CD = RPT.ACTY_CD
    WHERE ACTY.ACTY_TXT IN ('PUR , 'RED' )
    AND Hdr_CD IN ( 0 , 1 )
    AND INPUT_SRC_CD = 5
    group by acty.ACTY_txt
    with ur
    select acty.ACTY_txt
    , sum(RPT.AMT) as amt
    FROM TABEL-A RPT
    JOIN TABLE-B ACTY
    ON ACTY.ACTY_CD = RPT.ACTY_CD
    WHERE ACTY.ACTY_TXT IN( 'CAP' , 'DIV' )
    AND Hdr_CD = 2 AND INPUT_SRC_CD = 5
    group by acty.ACTY_txt
    with ur
    select acty.ACTY_txt
    , sum(RPT.AMT) as amt
    FROM TABEL-A RPT
    JOIN TABLE-B ACTY
    ON ACTY.ACTY_CD = RPT.ACTY_CD
    WHERE ACTY.ACTY_TXT IN( 'REV' )
    AND Hdr_CD = 4 AND INPUT_SRC_CD = 5
    group by acty.ACTY_txt
    with ur

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What about using UNION or UNION ALL? Sometimes the performance is better with UNION ALL, even if you can combine them all into one query.
    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
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about something like:
    Code:
    select acty.ACTY_txt 
    , sum(RPT.AMT) as amt 
    FROM TABEL-A RPT 
    JOIN TABLE-B ACTY 
    ON ACTY.ACTY_CD = RPT.ACTY_CD 
    WHERE ACTY.ACTY_TXT IN ('PUR , 'RED', 'CAP' , 'DIV', 'REV' )
    AND Hdr_CD IN ( 0, 1, 2, 4 ) 
    and ((ACTY.ACTY_TXT IN ('PUR , 'RED')
      AND Hdr_CD IN ( 0, 1))
      or (ACTY.ACTY_TXT IN( 'CAP' , 'DIV' )
      AND Hdr_CD = 2)
      or (ACTY.ACTY_TXT IN( 'REV' )
      AND Hdr_CD = 4))
    AND INPUT_SRC_CD = 5 
    group by acty.ACTY_txt 
    with ur
    Dave

  4. #4
    Join Date
    Mar 2010
    Posts
    7

    Thumbs up Thank you

    Thank you Dave. The SQL retrieved the result set as i expected.

Posting Permissions

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