Oracle correlated subquery in FROM list

Question

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

Answer 1

Sub-queries within a FROM clause cannot refer to other tables from the same FROM clause. Removing the ObsMax2.pId = Obs.pId clause should resolve the problem and from I can tell will give you exactly the same result since the same clause is in the join condition. However, as you mention, you may run into performance issues with having the GROUP BY in the sub-query.

From what I can tell, you're trying to get the individual pID/hdId records from ml.Obs with the largest obsDate that's less than {EndDate}. In that case, what about moving the sub-query into the WHERE clause where you can correlate it? E.g.:

select ...
from
  ml.Person Person
  join ml.Obs on Person.PID = Obs.pId
where Obs.obsDate = (
    select max(obsDate)
    from ml.Obs Obs2
    where Obs2.pId = Obs.pId
      and obs2.hdId = Obs.hdId
      and Obs2.obsDate < {EndDate})

Answer 2

You've prefixed many of your tables with "ml." but not everywhere (the first join, for example). Assuming you need that (for user/permissions/whatever):

JOIN ml.Obs ON Person.pID = ml.Obs.pId

or

JOIN ml.Obs Obs ON Person.pID = Obs.pId

There are other places where this would be needed too.

If this isn't the case, remove them from your query because they're irrelevant and distracting.

Answer 3

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
/