Re: Legal syntax or fluke?

I think it probably should be illegal because of the last subquery:

    select * from .....t3 having avg(t3.balance)

technically this expands to

    select ......, balance from t3 having avg(balance) ...

i.e. a column and the aggregate of the column both in the query.

However I suspect it works because the Oracle optimiser decides that the intent of your query is not to select values, but to check for existence and does not attempt to review the content of the select list.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Mike Schuler wrote in message <36D812C2.6B8B_at_dbsurfer.com>...
>/* Display duedate for DBA.cust_order with each average of balance
>by duedate between 400 and 500 */
>
>select t1.duedate
>from "DBA".cust_order t1
>where t1.duedate in
> (select t2.duedate
> from "DBA".cust_order t2
> where not exists
> (select *
> from "DBA".cust_order t3
> where t2.duedate = t3.duedate
> having avg(t3.balance) not between 400 and 500))
>
>I have a product that generates the above syntax from the input
>comment, but it only works on Oracle8 (not DB2 and not Sybase).
>Regardless of the sanity of the statement - is it actually legal in
>ANSI SQL? Thanks for any hints.
>
>
>--
>Mike Schuler
>http://www.dbsurfer.com
>Voice 604.926.2676
>Sent from Netscape:Oahu node
Received on Sat Feb 27 1999 - 12:16:49 CST