Menu
I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of the databases something like the following works.
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=a.col1;
In this query tableA.col1 is of type number and tableB.col2 is of type varchar. This works fine in two of the databases but not in the third. In the third I get (ORA-01722) error. In the third I need to do something like...
select a.col1, b.somecol
from tableA a inner join tableB b on b.col2=to_char(a.col1);
This works in all the databases. The question I have is why? The above is a simplified query, and the real query is a little more complex and retrieves a lot of data, hence the first version is much faster. If I could get that to work in all environments it would be great.
Does anyone know why this may work in some oracle databases and not others without the cast on the datatype? Is there a global setting that enables such behavior?
288
TO_CHAR (number) converts n to a value of VARCHAR2 datatype, using the optional number format fmt . The value n can be of type NUMBER , BINARY_FLOAT , or BINARY_DOUBLE . If you omit fmt , then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
SELECT * FROM table1 JOIN table2 ON NVL ( LTRIM (table1. col1, '0') , '0' ) = TO_CHAR (table2. col1) ; This assumes that table1.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
The biggest reason is that when stored in a VARCHAR2 datatype, you can retain your leading zeros, whereas in a NUMBER datatype, you cannot. For instance, many zip codes on the east coast start with a zero.
One reason why implicit conversions fail is when the joining varchar column contains data which is not numeric. Oracle handles number to varchar2 joins by converting the strings (check out Gary's citation in his comment), so it actually executes this :
select a.col1, b.somecol
from tableA a inner join tableB b on to_number(b.col2)=a.col1;
If tableB.col2 contains values which are not numeric - seems quite likely, it is a string after all - then it will hurl ORA-01722: invalid number. By explicitly casting the number column to a string you short-circuit Oracle's default behaviour.
The fact that you don't get this problem in your first two environments is a matter of luck not configuration. It could strike at any time, because it only requires one non-numeric string to break the query. So really you ought to run with the explicit conversion in all environments.
As for performance, you could build a function-based index ...
create index whatever_idx on tableA ( to_char(col1) )
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
