Avoiding Correlated Subquery in Oracle

Question

In Oracle 9.2.0.8, I need to return a record set where a particular field (LAB_SEQ) is at a maximum (it is a sequential VARCHAR array '0001', '0002', etc.) for each of another field (WO_NUM). To select the maximum, I am attempting to order in descending order and select the first row. Everything I can find on StackOverflow suggests that the only way to do this is with a correlated subquery. Then I use this maximum in the WHERE clause of the outer query to get the row I want for each WO_NUM:

SELECT lt.WO_NUM, lt.EMP_NUM, lt.LAB_END_DATE, lt.LAB_END_TIME
FROM LAB_TIM lt WHERE lt.LAB_SEQ = (
   SELECT LAB_SEQ FROM (
      SELECT lab.LAB_SEQ FROM LAB_TIM lab WHERE lab.CCN='1' AND MAS_LOC='1'
          AND lt.WO_NUM = lab.WO_NUM ORDER BY ROWNUM DESC
   ) WHERE ROWNUM=1
)

However, this returns an invalid identifier for lt.WO_NUM error. Research suggests that ORacle 8 only allows correlated subqueries one level deep, and suggests rewriting to avoid the subquery - something which discussion of selecting maximums suggests can't be done. Any help getting this statement to execute would be greatly appreciated.

Answer 1

Your correlated subquery would need to be something like

SELECT lt.WO_NUM, lt.EMP_NUM, lt.LAB_END_DATE, lt.LAB_END_TIME
FROM LAB_TIM lt WHERE lt.LAB_SEQ = (
   SELECT max(lab.LAB_SEQ)
     FROM LAB_TIM lab 
    WHERE lab.CCN='1' AND MAS_LOC='1'
      AND lt.WO_NUM = lab.WO_NUM 
  )

Since you are on Oracle 9.2, it will probably be more efficient to use a correlated subquery. I'm not sure what the predicates lab.CCN='1' AND MAS_LOC='1' are doing in your current query so I'm not quite sure how to translate them into the analytic function approach. Is the combination of LAB_SEQ and WO_NUM not unique in LAB_TIM? Do you need to add in the predicates on CCN and MAS_LOC in order to get a single unique row for every WO_NUM? Or are you using those predicates to decrease the number of rows in your output? The basic approach will be something like

SELECT *
  FROM (SELECT lt.WO_NUM, 
               lt.EMP_NUM, 
               lt.LAB_END_DATE, 
               lt.LAB_END_TIME,
               rank() over (partition by wo_num
                                order by lab_seq desc) rnk
          FROM LAB_TIM lt)
   WHERE rnk = 1

but it's not clear to me whether CCN and MAS_LOC need to be added to the ORDER BY clause in the analytic function or whether they need to be added to the WHERE clause.

Answer 2

This is one case where a correlated subquery is better, particularly if you have indexes on the table. However, it should be possible to rewrite correlated subqueries as joins.

I think the following is equivalent, without the correlated subquery:

SELECT lt.WO_NUM, lt.EMP_NUM, lt.LAB_END_DATE, lt.LAB_END_TIME
FROM (select *, rownum as r
      from LAB_TIM lt
     ) lt join
     (select wo_num, max(r) as maxrownum
      from (select LAB_SEQ, wo_num, rownum as r
            from LAB_TIM lt
            where lab.CCN = '1' AND MAS_LOC = '1'
           ) 
     ) ltsum
     on lt.wo_num = ltsum.wo_num and
        lt.r = ltsum.maxrownum

I'm a little unsure about how Oracle works with rownums in things like ORDER BY.