I have a table tblA ( ID varchar2(5), DESC varchar2(200) ) which includes data:
'1', 'One' '2', 'Two'
My query is something like:
select anotherTbl.ID, T.txt from anotherTbl, (select 0 as ID, 'Undefined' as txt from dual union select to_number(tblA.ID) as ID, tblA.DESC as txt from tblA ) T where anotherTbl.extID = T.ID and T.ID = 1 -- Actually 1 is a parameter
This query works in Oracle 12. However, in Oracle, an error ORA-01722: invalid number was raised.
More than weird, if I change to_number(tblA.ID) to to_number(trim(tblA.ID)), it would run sound in both Oracle 11 and Oracle 12!
I checked each IDs in tblA, just '1', '2', '3', ..., and '20', no spaces, no offence characters.
My guess is that Oracle 11 does not support this comparison, but could anyone please explain in a little more detail?