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.
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))
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