What could be different ?

[hedyash]

Hello,


I am trying to execute the SQL statement

SQL > select * from mytable where col1 = 1234438;

col1 is varchar2(20).

The query works and retreives the records on the development environment; whereas it gives an error ORA 1722 - invalid number in the test environment. It works fine if the value is enclosed in quotes.

I would like to know what could be different between the two environments?

Any input on this is greatly appreciated.

Thanks in advance.


I am able to perform the query on other varchar2 columns; it is failing only on this column.

- Thanks

[Edited by hedyash on 01-26-2001 at 03:03 PM]

[mber]

I clearly rember this issue be posted early in this forum, but I don't quite get hold of the date. You can try to find them in the former last two months threads.

Thanks

[akkerend]

The difference is probably in the values of col1.
You try to compare a NUMBER with a VARCHAR2. You don't explicitly convert the literal number (1234438) to character. In this case Oracle converts the varchar2 database column (col1) to number. If one of the values in col1 is not numeric, you get this error.
Another problem with this implicit conversion is that indexes cannot be used in the query.

[hedyash]

Thanks Akkerend. One of the values was a character string.