Re: Operator and condition precedence

Predicates are evaluated in reverse order. Since and aa.col_2a > ' ' is always false (comparison to NULL is always false)
to_number(aa.col_2a) = bb.col_2b is never evaluated.

try this
select * from dual
where to_number('abc') > 0
and 'xyz' > '';

Mike

On Tue, Sep 8, 2009 at 1:49 PM, Rich Jesse <rjoralist_at_society.servebeer.com>wrote:

> Hey all,
>
> A coworker asks me about casting an NCHAR column to NUMBER in a query on
> 10.1.0.5.0 (AIX), so I of course offer up the syntax of TO_NUMBER.
> Somewhat
> knowing the data, I warn that conversions on columns of all spaces will
> fail. But for some reason, this works:
>
> select aa.stuff, bb.stuff
> from table_a aa,
> table_b bb
> where aa.col_1a = bb.col_1b
> and to_number(aa.col_2a) = bb.col_2b
> and aa.col_3a = bb.col_3b
> and aa.col_4b = bb.col_4b
> and aa.col_6a in ('YY','ZZ')
> and aa.col_2a > ' '
> and aa.col_5a <> bb.col_5b;
>
> Looking at the docs:
>
>
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/conditions001.htm#sthref874
>
> ...tells me that the above would attempt to convert an AA.COL_2A to a
> number
> before filtering it out from the AA.COL_2A > ' ' part, but it obviously
> doesn't. And there are known values of AA.COL_2A where it's all spaces.
>
> The above statement works. My question is "How?"
>
> Thanks!
> Rich
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l