Urgente to_number, cast(as number) no me funcionan

[jucas1987]

Buenos dias.

Tengo el siguiente problema.
Necesito enlazar dos tablas, para lo cual uso un inner join, los campos por los cuales voy a realizar el inner join son varchar2 de 7 bytes. Tengo que convertir estos a numericos para poder realizar el inner, ya que en una tabla aparecen de la siguiente manera 033 y en otra 33 y como ya sabran en varchar esto nunca va a ser igual; por lo tanto he realizado un cast y no me ha funcionado, he realizado un To_number y tampoco. de la misma manera he cambiado el to_number a integer, float y demas campos numericos que se me han ocurrido pero nada.

El query con el que estoy realizando la prueba es el siguiente:

SELECT ID_ACTIVIDAD, NOMBRE FROM TB_ACTIVIDADES
INNER JOIN XTB_RPT_DETALLE ON TO_NUMBER(TB_ACTIVIDADES.CODIGOOPERACION) = TO_NUMBER(XTB_RPT_DETALLE.COD_OPERACION)
WHERE CAST(COD_OFICNA AS NUMBER)=3

si quito los To_number, o cast si lo tuviera. me funciona, pero como se han de imaginar el resultado que obtengo no es el esperado, ya que la comparacion no es correcta.

El ERROR que obtengo al ejecutar el query, asi como esta es el siguiente:

ORA - 01722 : NUMERO NO VALIDO
01722.00000 "INVALID NUMBER"

Esto es algo con caracter urgente, asi que si me pudiesen colaborar se los agradeceria enormemente.

[huesos52]

jucas1987

Lo que pasa es que puedes tener algunos registros del campo varchar que no puedan ser convertidos a numericos.

Mira este ejemplo

Código SQL:

SQL> CREATE TABLE conversion(num1 INTEGER,num2 varchar2(10));
 
Tabla creada.
 
SQL> INSERT INTO conversion VALUES(33,'033');
 
1 fila creada.
 
SQL> SELECT *FROM conversion WHERE num1 = CAST(num2 AS NUMBER);
 
      NUM1 NUM2
---------- ----------
        33 033
 
SQL> SELECT *FROM conversion WHERE num1 = to_number(num2);
 
      NUM1 NUM2
---------- ----------
        33 033
 
SQL> INSERT INTO conversion VALUES(55,'055t5');
 
1 fila creada.
 
SQL> SELECT *FROM conversion WHERE num1 = CAST(num2 AS NUMBER);
ERROR:
ORA-01722: numero no valido
 
 
 
ninguna fila seleccionada
 
SQL> SELECT *FROM conversion WHERE num1 = to_number(num2);
ERROR:
ORA-01722: numero no valido
 
 
 
ninguna fila seleccionada
 
SQL>
 

Fijate que el error empieza a salir cuando insertamos una t entre el numero y ya no puede ser convertido.

Debes revisar que registro(s) te interfieren en el casteo del campo. Por que las funciones de conversiòn parece que las estas utilizando bien.

saludos

[jucas1987]

No nada huesos.

todos los campos que tengo en la base son numeros, lo unico que los diferencia como ya dije anteriormente, es que unos tiene ceros y otros (0033 y 33).

La verdad no tengo la menor idea de que pueda ser, las funciones cast y to_number las he usado infinidad de veces, solo esta vez tengo problemas, si me puedes dar otro consejo te estare muy agradecido...

[huesos52]

Puede estar pasando que en el campo varchar tengas numeros con decimal y la configuraciòn regional de oracle no detecte un punto o una coma en el lugar correcto.

Por ejemplo..

3.200 podrìa ser 3.2 0 3200. o caso inverso.

mira este ejemplo

Código SQL:

SQL> SELECT *FROM conversion;
 
      NUM1 NUM2
---------- ----------
        33 033
      1712 1712.000
SQL>  SELECT *FROM conversion WHERE num1 = CAST(num2 AS NUMBER);
ERROR:
ORA-01722: numero no valido
 

Pero si cambiamos el punto por la coma funciona.

Código SQL:

SQL>  SELECT *FROM conversion WHERE num1 = CAST(REPLACE(num2,'.',',') AS NUMBER);
 
      NUM1 NUM2
---------- ----------
        33 033
      1712 1712.000
 

Esto se da, por que el punto (.) juega el papel de separador de miles y no separador decimal. si fuese separador de miles, esto nunca podrìa convertirse a numero, en cambio con la coma si se podrìa.

Si en tu configuración regional es al revés, debes invertir en el replace la coma y el punto.

saludos y espero soluciones el problema

[jucas1987]

Nada huesos, los campos que tengo en la base son sencillos no tienen ni puntos ni comas, solo son numeros maximo de 3 cifras.

[huesos52]

jucas

te recomiendo hacer la comparación mas detallada. Es decir... con registros que estes seguro que no tienen problemas filtralos en el where para ver si te sale algo.

El error es claro y eso es lo que dice... puede que no lo veas a simple vista.