select clause x where clause valid numbers in SQL

I wonder if somebody can shed some light on this:

If I execute:

    select *
    from dba_profiles
    where resource_name = 'IDLE_TIME' and

           limit != 'UNLIMITED' and
           to_number (replace (limit, 'UNLIMITED')) > 0

I get an Invalid Number error.

If I execute:

    select to_number (replace (limit, 'UNLIMITED'))     from dba_profiles
    where resource_name = 'IDLE_TIME' and

           limit != 'UNLIMITED'

I simply get no rows selected, no errors!

My figure is Oracle evaluates the select clause AFTER it valuates the where clause as true for a given row. Since the value for the limit column may be 'UNLIMITED' or a valid number (in char format, a string), in the former case Oracle would try to make a to_number on a string generated by "replace ('UNLIMITED', 'UNLIMITED')" In the latter case, I think he would only work with numbers, when doing the number to char conversion.

What I find strange is: why get an invalid number error when I actually do to_number ('')?

P.S. What I need is a bit more complex than the queries above. These queries were build merely for this posting in the net!

Thanks a lot!

Andre Vincent
Software Design Rio (Rio de Janeiro) Received on Thu Apr 18 1996 - 00:00:00 CEST