ORA-01722: Invalid number in oracle

Question

I have a table called TABLE_TEMP with column AMOUNT as varchar2(20) I am executing below query:

SELECT to_number(AMOUNT) amt FROM TABLE_TEMP WHERE  DC IN ('C','D');

Result for above query is

AMT
----
0
123
511
485
0

I want to fetch records where AMT > o and using below query for that

SELECT AMOUNT amt FROM TABLE_TEMP WHERE  DC IN ('C','D') and to_number(AMOUNT) > 0;

getting error ORA-01722: Invalid number Please suggest me..

Are you using o or 0? You have them both in your post... — Mureinik
After checking o/0 issue, if the error still persists, you may want to check if the amount column has any non-digit characters using the query: select amount from table_temp where regexp_like(amount, '[^[:digit:]]'). Just curious, why amount is declared as varchar2 as opposed to some numeric type? — ramana_k
i am using ZERO (0) only. I first select statement i am converting amount to to_number and displaying data. it is working fine. If i use same logic in WHERE clause why it is giving me INVALID NUMBER error. — user1463065
Some other row in the table has non-numeric data in the amount column. The database is free to evaluate predicates in whatever order it believes will be most efficient. So it is perfectly legal to evaluate the to_number predicate before the dc predicate. — Justin Cave

Ollie Ollie · Accepted Answer · 2015-08-04T09:51:04

If you are sure that all rows in your table where DC is in 'C' and 'D' are numeric then you could try:

WITH temp_res 
  AS (SELECT amount
        FROM table_temp
       WHERE DC IN ('C','D'))
SELECT amount amt 
  FROM temp_res 
 WHERE TO_NUMBER(amount) > 0;

ORA-01722: Invalid number in oracle

1 Answers