Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: to_number question

RE: to_number question

From: Wolfgang Breitling <>
Date: Thu, 15 Jul 2004 05:45:18 -0700
Message-ID: <>

Quoting Stephen.Lee_at_DTAG.Com:

> > -----Original Message-----
> > You were assuming that the filter would
> > be applied
> > before the function, but you can not assume that, you have to
> > explicitly
> > request it.
> I believe the purpose of the subquery was to "request it". The function
> was
> being applied to data that was not in the subquery. If this is part of the
> laws of SQL processing, "then the law is an ass".
These are the "rules". If you don't like them, go back to hierarchical databases.
Using subqueries does not impose any order on the processing. Just like the order of predicates in the sql does not determine the order in which they are evaluated (caution - this is an analogy!). The RDBMS engine is allowed to restructure the sql any which way it sees fit. If your sql is dependent on a particular order of processing then
a) it is not proper relational query because in a relational query the order of processing is irrelevant.
b) you have to make sure that the query can not be reqritten such that your required order is violated.

> I'm more inclined to say that this is a bug.
It is not. It is a misconception on your part.

> If you have an example of "request it" that you think
> is correct and shows mine to be wrong, I would be most interested in it.

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: select distinct nbr_cc_fop_name, (X - Y) xxx, (X+Y) yyy  from (
   select nbr_cc_fop_name

     , to_number(trim(nbr_cc_no_high)) X
     , to_number(trim(nbr_cc_no_low)) Y
     , rownum rn

   from nbr_cc
   where nbr_cc_fop_name in ('AX','MC','VI','DS')    )

or use the hint as Tanel suggested.
> > Just as you can not assume that the resultset is
> > returned in a
> > particular order, you have to explicitly request it with an
> > "order by" clause.
> I would be most interested to see how an "order by" would fix this
> situation.

This was meant as an anlogy, not a fix for this situation.


Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Jul 15 2004 - 07:42:08 CDT

Original text of this message