ORA-01722: invalid number in to_number

Question

I’m trying to connect data from two tables by invoice number,

LOC         NOT NULL VARCHAR2(40)
INV_ORG            NUMBER

Like this it works fine:

SELECT     oh.inv_org,e.loc
FROM       headers oh, sites e
WHERE
AND e.location = TO_CHAR(oh.inv_org);

But when I try to convert char to number I get ORA-01722: invalid number

SELECT     oh.inv_org, e.loc
FROM       headers oh, sites e
WHERE
AND to_number(e.loc) = oh.inv_org;

Error report

ORA-01722: invalid number

I don't know why because in select, conversion works also fine

INV_ORG  110992
LOC  110992
OH.INV_ORG-TO_NUMBER(E.LOC) 0

Answer 1

Check your data, it's possible to have a value in e.loc cannot be converted to number.

By the way, below select gave ORA-01722:

SELECT to_number('test') FROM dual

Answer 2

If this doesn't work:

SELECT     oh.inv_org, e.loc
FROM       headers oh, sites e
WHERE
AND to_number(e.loc) = oh.inv_org;

it means that there are e.loc values that can't be converted to numbers (such as letters). Therefore, you should use query which works (as there's no problem in converting oh.inv_org to a string):

SELECT     oh.inv_org,e.loc
FROM       headers oh, sites e
WHERE
AND e.location = TO_CHAR(oh.inv_org);

However, the whole story means that current data model isn't correctly set. Columns you are joining might need to be used in a foreign key constraint of some kind, i.e.

• there should be a table that contains locations,
• it should have a primary key, and
• that primary key column should be referenced from both headers and sites tables.

If that was the case, you wouldn't have problems you have now.

P.S. Oh, yes - and, try to switch to ANSI joins; won't fix your current problems, but will pay off in the future.

SELECT oh.inv_org,e.loc
FROM   headers oh join sites e on e.location = to_char(oh.inv_org);