ORA-01722: invalid number error when comparing varchar and number
MadhuriJul 15 2010 — edited Jul 15 2010Hi,
We are upgrading our databases to 10g and have issue with the below query
SELECT VALUE FROM table1 WHERE SUBSTR (VALUE, 10) = 1.
In table1, VALUE is the column name and it will have data ending with number. ex: SIGNATURE1, SIGNATURE2, SIGNATURE3, SIGNATURE4....SIGNATURE9.
As output of substr is varchar, Is comparing it with number throwing the error?
If so, the same query is working perfect in another 10g database which we migrated into.
Please help.
Thanks,
Madhuri