ORA-01722: invalid number when using to_number

0
votes

I am trying to find the max project code in a table using project codes and a customer code like so 

SELECT DISTINCT       Max(to_number(translate(substr(proj_code,7),'0123456789','0123456789')))
    FROM
        proj_ca
    WHERE
        proj_code LIKE 'CUST43_%'
        AND cust_code = 'CUST';

This code is supposed to return 97 When I run this code i get ORA-01722 invalid number, however when I do 42 or 44 it returns the proper number. I'm not sure why the "to_number" is throwing an error.

I have narrowed it down to the "to_number" part. If I remove that it returns 97 without any issues.

3
sqloracle
Do you have rows for CUST4397 with code CUST and something else; but the other ones you can get successfully only have CUST? Or other things that start with CUST4397 but sort before it - i.e. what do you see without the max? - Alex Poole
What is translate supposed to do? The way you wrote it, it does absolutely nothing. I imagine you think it does something, and perhaps that's the issue (or one issue, anyway). - mathguy
what version of Oracle are you on? I ask because in 12.2, to_number has been extended so it can handle conversion errors much better. - Boneist
@AlexPoole all the project codes have numbers after. So all the rows contain CUSTXXXX. Without max it returns all the rows that contain CUST43XX. - olivia.dayaram
@mathguy I didn't write the code. Can you explain why it doesn't do anything? - olivia.dayaram

3 Answers

2
votes

Oracle's TRANSLATE function will not do anything to characters that are in the input string but not in the list of characters to translate from. So this:

select translate(substr('CUST4397B',7),'0123456789','0123456789') 
from dual;

will return "97B", not "97".

Try putting this into your code instead:

select regexp_replace(substr('CUST4397B',7),'[^0-9]','')  answer
from dual;

answer
------
97
1
votes

A slightly different approach which is, I think, a bit more flexible would be to use

SELECT MAX(TO_NUMBER(REGEXP_SUBSTR(PROJ_CODE, '[0-9]{2}', 1, 2)))
  FROM PROJ_CA
  WHERE PROJ_CODE LIKE 'CUST43%' AND
        CUST_CODE = 'CUST'

Here the position of the numbers within the string are not hardcoded - instead, the second group of two digits found in the string is extracted, which is the case of CUST4397B is 97.

dbfiddle here

0
votes

Suppose the input was CUST43_42 translate is going to return the output as '_42' which cannot be converted to a number, hence to_number throws invalid number. Try SUBSTR (proj_code, 8)