select from varchar2 column with numeric value sometimes gives invalid number error
- by Rene
I'm trying to understand why, on some systems, I get an invalid number error message when I'm trying to select a value from a varchar2 column while on other systems I don't get the error while doing the exact same thing.
The table is something like this:
ID Column_1 Column_2
1 V text
2 D 1
3 D 2
4 D 3
and a query:
select ID
from table
where column_1='D'
and column_2 = :some_number_value
:some_number_value is always numeric but can be null.
We've fixed the query:
select ID
from table
where column_1='D'
and column_2 = to_char(:some_number_value)
This original query runs fine on most systems but on some systems gives an "invalid number" error. The question is why? Why does it work on most systems and not on some?
