ORA-01722- Invalid Number

Question

I have a query in the package throwing error

ORA-01722 : invalid number.

I tried many ways but no luck.

select h.column  
  from table1 h,table2 c 
 where c.created_date='17-MAY-17' 
   and nvl(c.acct_nmbr,0)=nvl(to_number(h.v_acct_num),0) 

• c.acct_nmbr is of NUMBER(19,0)
• h.v_acct_num is of varchar2(4000 byte)

Please suggest me the solution

Answer 1

Obviously v_acct_num column contains non-numeric values. to_number() function has restriction that the value in the argument shouldn't contain any non-numeric character, otherwise the system hurls with ORA-01722 : invalid number. If you're sure about the data matches when non-numeric characters are extracted then use regexp_replace(t2.v_acct_num,'[^[:digit:]]') within the collation of the join's on clause :

select t2.column  
  from table1 t1
  join table2 t2 on nvl(t1.acct_nmbr,0) = nvl(regexp_replace(t2.v_acct_num,'[^[:digit:]]'),0)
 where t2.created_date = date'2017-05-17'

Use ANSI-92 standard for Join, and ISO-8601 standard for date syntaxes.

Answer 2

Identify what data is non-numeric:

select * from table1 h
where regexp_like(h.v_acct_num, '[^0-9]')

and delete them

.

Hope it helps.

Answer 3

If you are using Oracle 12.2 or higher then you can use the TO_NUMBER with ON CONVERSION ERROR clause as follows:

SELECT
    H.COLUMN
FROM
    TABLE1 H
    JOIN TABLE2 C 
    ON NVL(C.ACCT_NMBR, 0) = NVL(TO_NUMBER(H.V_ACCT_NUM DEFAULT -999 ON CONVERSION ERROR), 0)
    -- use any number which is impossible in your column 
    -- so that it do not match any random records.
WHERE
    C.CREATED_DATE = DATE '2017-05-17';

Note:

• Use standard ANSI joins
• Use DATE literal wherever you want to convert string to date or TO_DATE function

Cheers!!