Re: How do you like this SQL?

From: dombrooks <dombrooks_at_hotmail.com>
Date: Fri, 14 Dec 2012 02:17:41 -0800 (PST)
Message-ID: <50158920-9ac8-41ad-b4ee-7eaab3bf83d1_at_googlegroups.com>

It's very hard to condemn a snippet of SQL.

It may be a big table and we know nothing about the statement but it's quite possible that Oracle is able to push predicates into this inline view.

If you don't write a lot of SQL, it might be difficult to appreciate what the development issue is here.

Presumably, the intention of the developer is to get the most recent comment for the POC.PO_ID,POC.LINE_ITEM_PO.

Unfortunately, Oracle restricts the level to which you can refer to an outer table from a subquery so what some might consider an obvious way to do this is via a scalar subselect and a traditional top N query, or similar, is not possible, e.g:

SELECT ...
, (SELECT comment

      FROM   (SELECT poc.comment
              FROM   po_comment poc
              WHERE  poc.po_id     = id_from_main_query
              AND    poc.line_item = line_item_from_main_query
              ORDER BY row_timestamp DESC)
      WHERE  ROWNUM = 1) comment
      ....

FROM .... In general, you can't do this because the reference to id_from_main_query and line_item_from_main_query because the level is too deep.

So, you could use a function to do the lookup or you could use KEEP DENSE RANK. E.g. something like
SELECT ...
, (SELECT MAX(comment) KEEP (DENSE_RANK FIRST ORDER BY row_timestamp DESC) OVER ()

      FROM   po_comment poc
      WHERE  poc.po_id     = id_from_main_query
      AND    poc.line_item = line_item_from_main_query) comment
      ....

FROM .... But whether it is sensible/desirable to do these lookups as a forced nested loop equivalent depends on the bigger picture. Received on Fri Dec 14 2012 - 11:17:41 CET