RE: to_number question

Of course it works. It is the proper way (without hints) of requesting (and ensuring) that Oracle to evaluate the subqueries on their own and not merging them with the rest of the query. Just as "order by" is the proper way of requesting (and ensuring) the resultrows to be returned in a particular order.

The basic tenet of relational databases, what I have tried to explain in this last series of posts, is that you must not assume any particular order of execution of your query, or components of your query. Any restructuring of the sql syntax is merely superficial, the fact that it sometimes works in causing the optimizer to change the access plan notwithstanding. It may quit working with the next upgrade to the optimizer.

Quoting Stephen.Lee_at_DTAG.Com:

> > -----Original Message-----
> > One of the ways to make sure that a subquery is evaluated on
> > its own and not
> > flattened (unnested) into the whole query is to use the
> > rownum pseudocolumn:
>
> I got around to trying this, and it works too.
> What a fine list group this is.
>

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------