Re: How do you like this SQL?

"John Hurley" <johnthehurley_at_gmail.com> wrote in message news:bd873b2b-24bd-43e5-a0ba-9329eb72c616_at_f19g2000vbv.googlegroups.com...
| One of my developers ... part of a very long piece of SQL near the
| end ... lots of inline views ... and this creative piece of code:
|
| (
| SELECT POC.PO_ID, POC.LINE_ITEM_PO, POC.COMMENT_PO,
| POC.DATE_ADD_COMMENT
| FROM PO_COMMENT POC
| WHERE (POC.PO_ID,POC.LINE_ITEM_PO,POC.ROW_TIMESTAMP)
| IN
| (SELECT A.PO_ID,A.LINE_ITEM_PO,MAX(A.ROW_TIMESTAMP)
| FROM PO_COMMENT A
| WHERE A.TYPE_PO_COMMENT='LE' GROUP BY A.PO_ID, A.LINE_ITEM_PO)
| ) T193
|
| Plus of course ... PO_COMMENT is a very big table ...

There are a couple of ways in which the code fragment might be undesirable - is it the possible logic error, or the possible performance impact you are trying to highlight - but there's nothing inherently bad about it that an outside observer should condemn without first checking context.

(The fact that PO_COMMENT is a very big table could be irrelevant if comments of type 'LE' were very rare).

-- 

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543