Multiple outer joins in DB2 view DDL?

CHKelley · #1 (**permalink**) 01-08-10, 17:21

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!

r937 · #2 (**permalink**) 01-08-10, 17:26

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'

CHKelley · #3 (**permalink**) 01-08-10, 17:35

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.

tonkuma · #4 (**permalink**) 01-10-10, 10:01

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'

r937 · #5 (**permalink**) 01-10-10, 10:09

Quote:

Originally Posted by tonkuma

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

tonkuma · #6 (**permalink**) 01-10-10, 10:23

Quote:

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.

r937 · #7 (**permalink**) 01-10-10, 10:27

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

tonkuma · #8 (**permalink**) 01-10-10, 10:36

Quote:

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é.
(I never used Caché. But, an explanation of Caché wrote that it is reverse with SQL Server.)

Internet Services

Web Development

Digital Marketing

Consumer Tech