Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Multiple outer joins in DB2 view DDL?

    I have the following (partial) DDL FROM/WHERE clause from an ORACLE view that needs to be re-written to use identical DB2 views (this is fictitious code for educational purposes). Note that "(+)" is Oracle right outer join:

    FROM ORACLE.EMPLOYEE A,
    ORACLE.PID B,
    ORACLE.DIV C
    WHERE A.PID = B.PID(+)
    AND B.DIV_CODE = C.DIV_CODE(+)
    AND C.STATUS_CODE = 'A'

    My question is this: How are multiple outer joins written in DB2 V8 view DDL to accommodate this? I have researched multiple IBM publications to find that only 2 tables are ever outer join'ed in a select clause. I have attempted the following (partial) DB2 DDL in a DB2 view:

    FROM
    DB2.EMPLOYEE RIGHT OUTER JOIN DB2.PID -- 1st outer join
    ON DB2.EMPLOYEE.PID = DB2.PID.PID,
    DB2.PID RIGHT OUTER JOIN DB2.DIV -- 2nd outer join
    ON DB2.PID.DIV_CODE = DB2.DIV.DIV_CODE
    WHERE
    DB2.DIV.STATUS_CODE = 'A'

    This DDL is obviously incorrect, but can anything like this be done in DB2 V8??

    THANKS!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
      FROM db2.div AS c
    LEFT OUTER
      JOIN db2.pid AS b
        ON b.div_code = c.div_code
    LEFT OUTER
      JOIN db2.employee AS a
        ON a.pid = b.pid
     WHERE c.status_code = 'a'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    r937-- I very much appreciate your quick response. I'll attempt the code you supplied as soon as possible. The actual view I'm re-writing has 8 outer joins and other joins as well, but I will use your code in my DDL. THANKS.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that LEFT and RIGHT should be exchanged.
    Here is an example in the article DB2 Viper 2 compatibility features
    Code:
    SELECT empname, deptname 
      FROM emp LEFT OUTER JOIN dept ON emp.deptid = dept.deptid;
    EMPNAME    DEPTNAME
    ---------- ----------
    Jones      Sales
    Meyer      Sales
    Newman     Marketing
    Feldman    -
    Hedges     -
    
      5 record(s) selected.
        
    SELECT empname, deptname
      FROM emp, dept WHERE emp.deptid = dept.deptid (+);
    EMPNAME    DEPTNAME
    ---------- ----------
    Jones      Sales
    Meyer      Sales
    Newman     Marketing
    Feldman    -
    Hedges     -
    
      5 record(s) selected.
    My quetion is:

    FROM ORACLE.EMPLOYEE A,
    ORACLE.PID B,
    ORACLE.DIV C
    WHERE A.PID = B.PID(+)
    AND B.DIV_CODE = C.DIV_CODE(+)
    AND C.STATUS_CODE = 'A'

    should be re-written to
    Code:
      FROM ORACLE.EMPLOYEE A
      LEFT OUTER JOIN
           ORACLE.PID B
       ON  A.PID = B.PID
      LEFT OUTER JOIN
           ORACLE.DIV C
       ON  B.DIV_CODE = C.DIV_CODE
       AND C.STATUS_CODE = 'A'
    or
    Code:
      FROM ORACLE.EMPLOYEE A
      LEFT OUTER JOIN
           ORACLE.PID B
       ON  A.PID = B.PID
      LEFT OUTER JOIN
           ORACLE.DIV C
       ON  B.DIV_CODE = C.DIV_CODE
     WHERE C.STATUS_CODE = 'A'

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tonkuma View Post
    should be re-written to
    ...
    or
    ...
    um, those two queries are the same

    stupid oracle plus signs, i can never remember which side they're supposed to go on...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Originally Posted by tonkuma
    should be re-written to
    ...
    or
    ...
    those two queries are different!
    Code:
      FROM ORACLE.EMPLOYEE A
      LEFT OUTER JOIN
           ORACLE.PID B
       ON  A.PID = B.PID
      LEFT OUTER JOIN
           ORACLE.DIV C
       ON  B.DIV_CODE = C.DIV_CODE
       AND C.STATUS_CODE = 'A'
    include rows of A and B, even if there is no row of C satisfying the conditions(no row with B.DIV_CODE = C.DIV_CODE or there is a row with B.DIV_CODE = C.DIV_CODE but C.STATUS_CODE <> 'A').

    or
    Code:
      FROM ORACLE.EMPLOYEE A
      LEFT OUTER JOIN
           ORACLE.PID B
       ON  A.PID = B.PID
      LEFT OUTER JOIN
           ORACLE.DIV C
       ON  B.DIV_CODE = C.DIV_CODE
     WHERE C.STATUS_CODE = 'A'
    exclude rows of A and B without matching rows of C(C.STATUS_CODE = 'A').
    it would be essentially same as inner join.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you're right, they are different, sorry

    i was still looking at how to translate that stupid plus sign into JOIN syntax, and you were using LEFT in both cases

    the answer to your latest question is to use AND, not WHERE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    stupid oracle plus signs, i can never remember which side they're supposed to go on...
    I agree with you.
    Same for "=*"(or "*=") syntax of (MS or Sybase)SQL Server or Cach&#233;.
    (I never used Cach&#233;. But, an explanation of Cach&#233; wrote that it is reverse with SQL Server.)
    Last edited by tonkuma; 01-10-10 at 11:43.

Posting Permissions

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