| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: to_number question
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
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.
-- 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 -----------------------------------------------------------------Received on Thu Jul 15 2004 - 07:42:08 CDT
 |
 |