Today I have received this error;
CLOUD*ANTO> sta G:\SQL_FRA\QUERY_0x0022
AND Z.CD_ELEMENT_PROVIDER IN
ERROR en línea 176:
ORA-01722: invalid number
This error is not new, at least for me, I have sometimes occurred, the problem is why we are making an implicit conversion data, the engine, in this case Oracle (also happens in SYBASE ASE / IQ, SQL Server, MySQL …) tries to convert values for one condition at the same type.
In this particular case is a NUMBER and VARCHAR in an IN clause, the solution is to make an explicit conversion like the one shown below;
WHERE Z.CD_ELEMENT_PROVIDER IN (SELECT TO_CHAR (TAB_ELEMENTS.CD_ELEMENT_PROVIDER) FROM TAB_SATELLITE_RECEIVER, ...
Must always make an explicit conversion so that these problems do not occur. Mainly development is people who should do it as a “best practices” and in general for all database engines.
HTH – Antonio NAVARRO