SMH SMH - 6 months ago 52
SQL Question

Error while converting number to char and adding an extra char oracle

I have two columns where VAL_ID is a varchar and id is number. I am trying to convert id to varchar and compare it to VAL_ID (id can be 123 and val can be either 123 or D123) so I have to do both conditions, but I am getting an error:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

My select:

select TO_NUMBER(VAL_ID) per_val, id p_val
from doc d inner join emp p
on p.VAL_ID = TO_CHAR(d.id)
or p.VAL_ID = CONCAT('D',TO_CHAR(d.id))

Answer Source

The problem is in your select. Why are you converting something that starts with a D to a number? You can just do:

select p.VAL_ID as per_val, d.id as p_val
from doc d inner join
     emp p
     on p.VAL_ID = TO_CHAR(d.id) or
        p.VAL_ID = CONCAT('D', TO_CHAR(d.id));

If you need the number component, then just use d.id.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download