SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01722 : Invalid Number - but only in where clause

593116Sep 5 2007 — edited Sep 5 2007

Can anyone explain why query 1 works, but query 2 and 3 fail with an invalid number error? To me, it makes no sense at all.

Query 1

select to_number(substr(account,1,5))
from table1

Query 2

select account
from table1
where to_number(substr(account,1,5)) > 0

Query 3

select account
from table1
where cast(substr(account,1,5) as numeric(19,2)) > 0

thanks

geoff

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 3 2007

Added on Sep 5 2007

24 comments

5,852 views