Select row as number in Oracle

Question

I found this strange behavior, and I'm puzzling this ... does anyone have any ideas?

Oracle 10g: I have two different tables, both columns named " TESTCOL " like Varchar2 (10) are not NULL.

If I execute this query in table1 , I get the correct results:

select * from table1 where TESTCOL = 1234; 

Note that I do not specifically post "1234" ... this is not a typo, this is a dynamically generated request, and I will try not to change it (at least not in the near future).

But if I run the same query on table2 , I get this error message:

 ORA-01722: Invalid number 

Both queries run in the same session, in the same database.

I joined these two tables with this column, and the connection is working fine, the only problem occurs when I try to use this condition.

Any ideas on what might differ from one table to another?

Thanks in advance.

Answer 1

If TESTCOL contains non-numbers, then Oracle may run into problems converting TESTCOL records to numbers. Because what he does internally is:

 select * from table1 where TO_NUMBER(TESTCOL) = 1234; 

If you are sure that 1234 cannot be expressed as a VARCHAR literal, try this instead to compare varchar values, not numeric ones:

 select * from table1 where TESTCOL = TO_CHAR(1234); 

Answer 2

The well-obvious TABLE2.TESTCOL contains values ​​that are not numbers. Comparing a string with a numeric literal creates an implicit conversion. Thus, any value in TESTCOL that cannot be discarded by a number will depend on ORA-1722.

This does not affect where you are comparing two tables, because you are comparing rows.

So, you have a couple of options that you no longer like. The most obvious answer is to clear the data, so TABLE2 does not contain a number. Ideally, you should combine this with changing the column to a numeric data type. Otherwise, you can change the generator to produce code that you can use against the shonky data model. In this case, this means wrapping literals in quotation marks if the associated column is of character data type.

Answer 3

You are attacking the dangers of implicit casting here.

With the expression testcol = 1234 you indicate that you want to treat testcol as a numeric column, so Oracle is trying to convert all the values ​​in this column to a number.

ORA-01722 is because at least one value in this column is not a number.

Even if you claim that it is “not a typo”, it’s really one thing. This is a syntax error.

You will need to declare your parameter as a string literal using single quotes: where testcol = '1234'

Creating the right conditions is the only solution to your problem.

Answer 4

The following should work. Just replace your place.

 select * from table1 where (select TO_NUMBER(TESTCOL) from table2 where "your where") = 1234;