SarekOfVulcan SarekOfVulcan - 12 days ago 5
SQL Question

Oracle correlated subquery in FROM list

I just tried to do a correlated subquery in the

FROM
clause of a
SELECT
statement in Oracle, but I was given an error indicating that I couldn't do the correlation (something to the effect that
Obs.pID
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

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,
              hdId,
              obsDate
              MAX(obsDate) over (partition by pId, hdId) maxDate
       FROM   ml.Obs
       WHERE  obsDate < {?EndDate}
       )
where  obsDate = maxDate
/