SarekOfVulcan SarekOfVulcan - 1 year ago 54
SQL Question

Oracle correlated subquery in FROM list

I just tried to do a correlated subquery in the

clause of a
statement in Oracle, but I was given an error indicating that I couldn't do the correlation (something to the effect that
was not recognized).

Should this work?

FROM ml.Person Person
JOIN ml.Obs ON Person.pID = Obs.pId
JOIN (SELECT ObsMax2.pId, ObsMax2.hdId
, MAX(ObsMax2.obsDate) as maxDate
FROM ml.Obs ObsMax2
WHERE ObsMax2.pId = Obs.pId
AND ObsMax2.obsDate < {?EndDate}
GROUP BY ObsMax2.pId, ObsMax2.hdId) ObsMax
ON Obs.pId = ObsMax.pId
AND Obs.hdId = ObsMax.hdId
AND Obs.obsDate = ObsMax.maxDate

My workaround would appear to be to make it a non-correlated subquery, and add criteria to the subquery that keeps it from running completely amuck, amuck, amu--oof Sorry.

I'd rather figure out how to properly correlate it, though, if possible - the view that works like that subquery takes forever to build.

Answer Source

You can achieve the intent of this part of the query by using an analytic function to identify the maximum obsDate for each pid and hdid.

It would be something like:

select ...
from   (
       SELECT pId,
              MAX(obsDate) over (partition by pId, hdId) maxDate
       FROM   ml.Obs
       WHERE  obsDate < {?EndDate}
where  obsDate = maxDate
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download