I'm not tagging this NF, because if I hadn't been doing this query in
Crystal Reports, it would have been in the Fox app it was based on.
:-)
I had never run into analytic functions before, but they appear to be
very useful.
I wanted to write the following query, but couldn't get it to work.
SELECT Person.pId
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
I asked on
http://stackoverflow.com/questions/611676/oracle-correlated-subquery-in-from-list.
They explained that I couldn't refer to a table in the same FROM list
when using a correlated subquery, and suggested rewriting it like
this.
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})
Before they suggested it, though, I came up with a variant on my
original query that worked pretty well. By moving all the selection
criteria for the Obs/ObsHead tables into the subquery, I reduced it to
a manageable level, and got a query cost of about 490.
SELECT
Person.pId,
, ObsHead.hdId, ObsHead.Name AS obsName
, Obs.obsDate, Obs.obsValue
FROM
ml.Person Person
JOIN ml.Obs ON Person.pID = Obs.pId
JOIN ml.ObsHead ON Obs.hdId = ObsHead.hdId
JOIN (SELECT ObsMax2.pId, ObsMax2.hdId, MAX(ObsMax2.obsDate) as maxDate
FROM ml.Obs ObsMax2
JOIN ml.ObsHead OHMax2 ON ObsMax2.hdId = OHMax2.hdId
WHERE ObsMax2.obsDate > {?EndDate} - INTERVAL '2' YEAR
AND ObsMax2.obsDate <= DATE {?EndDate)
AND OHMax2.Name IN (
'RZPF', 'HYZPF UPPER', 'LYZPF LOWER', ...)
GROUP BY ObsMax2.pId, ObsMax2.hdId) ObsMax
ON Obs.pId = ObsMax.pId
AND Obs.hdId = ObsMax.hdId
AND Obs.obsDate = ObsMax.maxDate
However, there was another suggestion that was made. It was so
different from what I was used to that it took me a while to get my
head around.
select ...
from (
SELECT pId,
hdId,
obsDate
MAX(obsDate) over (partition by pId, hdId) maxDate
FROM ml.Obs
WHERE obsDate < {?EndDate}
)
where obsDate = maxDate
Once I researched it a bit and figured out what it was doing, I tried
it in the main query, and got a cost of only 440.
For more details about analytic functions, see
http://www.oracle.com/technology/oramag/oracle/05-mar/o25dba.html.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
