Left Outer Join on Multiple Conditions

[kvang]

How can I do a left outer join on multiple conditions?

 

[SantaMufasa]

Bkou,

Can you give an example of the "multiple conditions" to which you refer?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kvang]

In my workflow, a work object is created and put into a queue that is worked automatically by a background process. Then the work object is routed to a "Review" queue where the user completes the work object. The work object can be escalated to a "Review Escalate" queue for a team leader to complete if the user cannot complete it.

So I have this table that keeps a record of each queue a work object enters.

Here's what I have so far:

SELECT *
FROM PROCESSLOG A, PROCESSLOG B, PROCESSLOG C
WHERE A.WOBNUM = B.WOBNUM AND A.ENQUEUETIME = B.ENDLOCKTIME
AND B.ACTION NOT IN ('Unpend','Send Back','SendBack')
AND (A.WOBNUM = C.WOBNUM AND TRUNC(A.ENQUEUETIME) = TRUNC(C.ENDLOCKTIME) AND D.STEPNAME IN ('Review Pend','Review Escalate') AND C.ACTION = 'Complete')   
AND TRUNC(A.ENQUEUETIME) = TO_DATE('10/10/2007','MM/DD/YYYY')
AND A.STEPNAME = 'Review'

Not all work objects will be routed to the "Review Escalate" queue. I am using C to look for work objects that were completed in the "Review Escalate" queue. So in my report that I will create, I will to display the time (ENQUEUETIME) the work object enters the "Review" step and the time (ENDLOCKTIME) when the work object was completed (whether it was completed in the "Review" or "Review Escalate" does not matter).

 

[kvang]

Ignore the query above. I meant to post:

SELECT *
FROM PROCESSLOG A, PROCESSLOG B, PROCESSLOG C
WHERE A.WOBNUM = B.WOBNUM AND A.ENQUEUETIME = B.ENDLOCKTIME
AND B.ACTION <> 'Send Back'
AND (A.WOBNUM = C.WOBNUM AND TRUNC(A.ENQUEUETIME) = TRUNC(C.ENDLOCKTIME) AND D.STEPNAME = 'Review Escalate' AND C.ACTION = 'Complete')   
AND TRUNC(A.ENQUEUETIME) = TO_DATE('10/10/2007','MM/DD/YYYY')
AND A.STEPNAME = 'Review'

 

[SantaMufasa]

bkou,

First, your code refers to a "D.STEPNAME"...There is no alias "D" for a table.

Next, I infer from your explanation that there are "A." rows that match "B." rows, but there might not be matching "C." rows. Regardless, you still want to see the "A." rows. Therefore, you need a "left outer join" on table-alias "C.", right? If so, then using the left-outer-join operator "(+)" on table "C." should do what you want:

SELECT *
  FROM PROCESSLOG A, PROCESSLOG B, PROCESSLOG C
 WHERE A.WOBNUM = B.WOBNUM
   AND A.ENQUEUETIME = B.ENDLOCKTIME
   AND B.ACTION NOT IN ('Unpend','Send Back','SendBack')
   AND (A.WOBNUM = C.WOBNUM (+)
        AND TRUNC(A.ENQUEUETIME) = TRUNC(C.ENDLOCKTIME(+))
        AND D.STEPNAME IN ('Review Pend','Review Escalate')
        AND (C.ACTION = 'Complete' or C.ACTION = null))   
   AND TRUNC(A.ENQUEUETIME) = TO_DATE('10/10/2007','MM/DD/YYYY')
   AND A.STEPNAME = 'Review'

Also, since left-outer joins produce "matching" NULL rows for table "C.", you'll notice that I also created a condition, "...C.ACTION = null" to accommodate a join for the rows where there is no "C." row.

Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kvang]

D.STEPNAME" should've been C.STEPNAME