Outer Joins with subqueries?

[mwolf00]

I have a table which tracks events - the data looks like this.

EEVENTID	EFROMSTAGE				ETOSTAGE					EEVENTTIME
3				Request Creation			Validation Check			6/26/2007 18:29
4				Validation Check			Initial Review				6/26/2007 18:29
12				Initial Review				Data Input					6/27/2007 15:51
21				Data Input					Undo System Chg		6/28/2007 21:09
22				Undo System Chg		Initial Review				6/28/2007 21:41
23				Initial Review				QA Rejection Rev			6/28/2007 21:43
24				QA Rejection Rev			Request Rejected			6/28/2007 21:45
26				Request Rejected			QA Clarify Review		7/2/2007 14:10
27				QA Clarify Review		Resolution Review		7/2/2007 16:12
28				Resolution Review		Data Input					7/2/2007 16:15
30				Data Input					Ready to be Flzd			7/16/2007 21:12

I need to be able to find times between matched pairs. For instance, I need the time spent on Data Input so I see that it got there at event 12 and left at event 21. I need to make sure not to use event 30. Can this be done with a subselect in the join?

This query tells me that a column may not be outer-joined to a subquery.

SELECT m.txtAssignedAnalyst
	   , m.efolderid
	   , f.efoldername
	   , f.estagename
	   , m.chLateSub
	   , m.dtdeadlinestart
	   , m.dtactualday1complete
	   , m.dtactualday2complete
	   , SUM(rbm_fGetBusinessDays(e3.eEventTime,e4.eEventTime,m.intRegionStart, m.intRegionEnd)) "Data Inp"
FROM RBM_MANAGEMENT m
JOIN EFOLDER f ON m.efolderid = f.efolderid
LEFT JOIN EEVENT e3 ON e3.efolderid = m.efolderid AND e3.etostage = 'Data Input' AND e3.etostage <> e3.efromstage
LEFT JOIN EEVENT e4 ON e4.efolderid = m.efolderid AND e4.eFROMstage = 'Data Input' AND e4.etostage <> e4.efromstage[red]
 and e4.eeventid = (select min(eeventid) from eevent where efromstage = 'Data Input' and efromstage <> etostage and eeventid > e3.eeventid)
[/red]
WHERE m.dtdeadlinestart BETWEEN TO_DATE('6/25/2007','mm/dd/yyyy') AND TO_DATE('7/1/2007','mm/dd/yyyy')
AND m.txtAssignedAnalyst = 'someUserName'
GROUP BY m.txtAssignedAnalyst
	  , m.efolderid
	   , f.efoldername
	   , f.estagename
	   , m.chLateSub
	   , m.dtdeadlinestart
	   , m.dtactualday1complete
	   , m.dtactualday2complete

Any assistance would be greatly appreciated.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[notadba]

HI Mwolf00,

I have found the way to get around this (which I am sure is really inefficient sql) is to make the outer join to a select statement.. This then makes the select clause a little messier as you need to select all columns that are being used in the 'on clause' as well as any being returned as columns. I have not tested this, but you can do a variation on this:
....

LEFT JOIN (select e4.eeventid, e4.efolderid, e4.eFROMstage, e4.etostage
from EEVENT e4
where e4.eeventid = (select min(eeventid) from eevent
where efromstage = 'Data Input'
and efromstage <> etostage
and eeventid > e3.eeventid))e5

ON e5.efolderid = m.efolderid
AND e5.eFROMstage = 'Data Input'
AND e5.etostage <> e4.efromstage

Hope this helps.

 

[taupirho]

I wonder if something simpler like this might identify your pairs of rows:-

select distinct
max(x.id1) over (partition by y.id2),
y.id2
from
(select eeventid id1,efromstage
from yourtable
where efromstage = 'Data Input') x,
(select eeventid id2,etostage
from yourtable
where etostage = 'Data Input') y
where id1 < id2





In order to understand recursion, you must first understand recursion.