Oracle - Consulta de campo numerico a campo alfanumerico

El caso es que tengo que hacer una consulta entre dos tablas. Tb1 y Tb2.

Tb1 tiene un campo numerido de 5 dígitos y Tb2 tiene ese mismo campo numérico de 5 dígitos pero precedidos por unas letras. Siempre 4 e iguales.

Mi pregunta es como puedo hacer la query por que al ser uno numerico y el otro no, no me deja por que dice que uno no es un numero .

Hay alguna funcion que no considere las 4 primeras posiciones ... o algo asi para poder comparar los campos

El campo de Tb1 es una cosa asi: 22334 y
El campo de Tb2 es como este: ISUM22334

Gracias por la colaboracion

creo que lo que quieres es esto:

SELECT CAMPO1,CAMPO2
FROM TB1, TB2
WHERE CAMPO1 = TO_NUMBER(SUBSTR(CAMPO2,5,7));

esto te compara el campo1 (numerico) con la parte del campo2 que contiene numeros, pasando antes el campo a numerico con TO_NUMBER.

espero te sirva.
Salu2.

Si mas o menos es eso ... pero el caso es que lo tengo que hacer con una subconsulta.

Me explico yo obtengo una tabla con una consulta en la que obtengo los valores con la parte alfanumerica y luego esta la otra tabla en la que existe el campo que tiene los valores solo numericos entonces al ponerle el TO_NUMBER y la subconsulta no me funciona .... me dice missing error.

Estoy haciendo una cosa asi:

select USP_FNAME from users_portals t where USR_ID = TO_NUMBER(select Distinct GHC_AUTHOR from gc_history_changes t where GHC_PROJECT=9999 and GHC_DATE >='01/03/2007' and GHC_DATE<='31/03/2007')

el TO_NUMBER debes colocarlo antes del campo que quieres convertir, no en la select:

select USP_FNAME from users_portals t where USR_ID = (select Distinct TO_NUMBER(SUBSTR(GHC_AUTHOR,4,3)) from gc_history_changes t where GHC_PROJECT=9999 and GHC_DATE >='01/03/2007' and GHC_DATE<='31/03/2007')

si los campos que quieres comparar son USP_FNAME, numerico, con el campor GHC_AUTHOR, varchar2, con esa select deberia servirte.

Salu2

Ante todo gracias por estar contestandome mad

Pero no es exactamente el campo USP_FNAME el que quiero comparar ... ese es el campo que quiero obtener de la comparacion entre USR_ID y la los campos de la tabla que me salen en la subconsulta, es decir, quiero sacar los USP_FNAME que tengan el USR_ID igual que los que me salen en la tabla de la subconsulta

Gracias de nuevo.

Efectivamente, me he confundido:
El campo USP_FNAME es el campo que quieres recuperar.
Es el campo USR_ID el que se compara con la subselect y es en la subselect donde se transforma el campo GHC_AUTHOR en su parte numerica a un campo numerico. Por lo demas deberia funcionar. Tambien es muy posible que no me este enterando muy bien de lo que quieres conseguir, hasta ahora creo que quieres recuperar el USP_FNAME de la tabla USERS_PORTALS cuyo contenido sea igual a la parte numerica del campo GHC_AUTHOR de la tabla GC_HISTORY_CHANGES. Si es eso con la select anterior deberia funcionar.

Salu2.

Pues eso es lo que quiero ... pero no me funciona. Evidentemente que algo hago mal pero no se el que.

TO_NUMBER(SUBSTR(GHC_AUTHOR,4,3)

Que es el SUBSTR y luego por que pones los números 4,3

En un manual veo que la sintaxix de TO_NUMBER es:

TO_NUMBER(cadena [,formato [,paramnls]])

Yo por cadena estoy poniendo la subconsulta ... y de lo otro no pongo nada por no saber que poner

asi no funciona ... ni con el SUBSTR o con el ...

select USP_FNAME from users_portals t where USR_ID = (select Distinct TO_NUMBER(GHC_AUTHOR) from gc_history_changes t where GHC_PROJECT=9999 and GHC_DATE >='01/03/2007' and GHC_DATE<='31/03/2007')

El substr coje una parte del campo que tu le pasa como parametro a la funcion, es decir,
en tu caso seria:

Si tu campo alfanumerico es asi: AHJY5541254
Y lo que quieres es comparar solo la parte numerica, entonces cojemos solo la parte del campo que nos insteresa con el SUBSTR y le decimos desde donde, hasta donde:

SUBSTR('AHJY5541254',5,7) nos retornaria unicamente la parte del campo de las posiciones 5 hasta la 7, es decir, '5541254' y si antes del substr le ponemos el TO_NUMBER nos retornaria lo mismo en numerico.

Espero te sirva.
Salu2.

Nada que eso no funciona ... y creo que no va a funcionar nunca, entre otras cosas por que mi campo alfanumericon no es un unico campo ... si no que es una tabla ... con muchos registros ... y entre otras cosas cada uno de esos registros no tiene la misma longitud ... lo unico que cumplen todos es que comiemzan por ISUM y el número, pero unos despues del isum tienen 4 cifras, otros tienen 5, etc

Salud

Vale ... ya separo la parte numerica de la alfabetica con el SUBSTR ... pero al ponerle el TO_NUMBER delante me da erro ... mi dice invalid number

para separar el campo usa:

SUBSTR(CAMPO,2,LENGTH(CAMPO))

aqui de retorna desde la posicion 2 hasta el final del campo.

El error de invalid number debe ser porque estas cogiendo desde una posicion que no es la correcta y te esta cojiendo un caracter.

Prueba y cuentame.
Salu2.

Pues si mad al final ha salido.

El problema era que siempre salia un usuario que es el admin ... que figura asi como admin y no tiene codigo numerico entonces al pasarlo a TO_NUMBER me daba error. He hecho la consulta excluyendo a este usuario ... he quitado la parte alfabetica con SUBSTR , lo he pasado a TO_NUMBER y he hecho la consulta principal indicando si estaban en la subconsulta con un IN y ha funcionado.

Muchas gracias por tu ayuda.

Xuco