Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    5

    Unanswered: Help With LEFT OUTER JOIN

    I'm trying to rewrite a UNION query with a LEFT OUTER JOIN since this is new with version 7.2 of DB2.
    You'll probably have to know my relationships in order to help me so I'll try to explain them the best that I can.
    There are a couple of tricky things in our table design.
    It's kind of huge so if I could get some help in the first part of the query where I select from the four tables that would be nice.
    Do I have to use nested table expressions or is there another way to do this?

    Here's my union query:
    ---------------------------------------------------
    EXEC SQL DECLARE c2 CURSOR FOR
    SELECT t1.appr_id, t1.agency_code, t1.purpose_code,
    t1.prog_code, t1.fund_code, t1.object_code,
    t1.adj_fund_amt, t1.dob_req_amt, t1.dob_req_fte,
    t1.cur_fte, t1.status, COUNT(t2.cut_id)
    FROM nysa.bp10t_appropriation t1,
    nysa.bp11t_approp_cuts t2,
    nysa.bp86t_negotiation t3,
    nysa.bp02t_agency t7
    WHERE t1.appr_id = t2.appr_id AND
    t1.status = :bp10.status AND
    t2.status = :bp11.status AND
    t7.status = :bp02.status AND
    t2.neg_code = t3.neg_code AND
    t3.current = 'Y' AND
    t1.agency_code = t7.agency_code AND
    t7.parent_code = :bp02.parent_code
    GROUP BY t1.appr_id, t1.agency_code, t1.purpose_code,
    t1.prog_code, t1.fund_code, t1.object_code,
    t1.adj_fund_amt, t1.dob_req_amt, t1.dob_req_fte,
    t1.cur_fte, t1.status
    UNION
    SELECT t4.appr_id, t4.agency_code, t4.purpose_code,
    t4.prog_code, t4.fund_code, t4.object_code,
    t4.adj_fund_amt, t4.dob_req_amt, t4.dob_req_fte,
    t4.cur_fte, t4.status, 0
    FROM nysa.bp10t_appropriation t4,
    nysa.bp02t_agency t8
    WHERE t4.agency_code = t8.agency_code AND
    t8.parent_code = :bp02.parent_code AND
    t4.status = :bp10.status AND
    t8.status = :bp02.status AND
    t4.appr_id NOT IN
    (SELECT t5.appr_id
    FROM nysa.bp11t_approp_cuts t5,
    nysa.bp86t_negotiation t6
    WHERE t5.neg_code = t6.neg_code AND
    t5.status = :bp11.status AND
    t6.current = 'Y')
    ORDER BY 2,3,4,5,6,7,8,9,10,12;
    ---------------------------------------------------------------------------
    Thanks in Advance
    John

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this, not tested (i don't have db2) --
    Code:
    SELECT ...
         , COUNT(t5.cut_id)
      FROM nysa.bp10t_appropriation t4
    INNER 
      JOIN nysa.bp02t_agency t8 
        ON t4.agency_code = t8.agency_code
       AND                  t8.parent_code = :bp02.parent_code 
       AND                  t8.status      = :bp02.status 
    LEFT OUTER 
      JOIN nysa.bp11t_approp_cuts t5
        ON t4.appr_id = t5.appr_id
       AND              t5.status = :bp11.status 
    LEFT OUTER 
      JOIN nysa.bp86t_negotiation t6
        ON t5.neg_code = t6.neg_code
       AND               t6.current = 'Y'
     WHERE t4.status = :bp10.status 
       AND t8.status = :bp02.status
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Posts
    5

    thanks

    Thanks A Lot rudy..

    It looks great so far!

    Thanks Again

    John

Posting Permissions

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