ORA-01722: invalid number - Subtracting Dates

Question

I have a query that I'm trying to write/get to run... I can get it to return results if I further the WHERE filters and add "rownum < 100". I can keep increasing that and it will expand the results.. However, if i remove it and let it run in its entirety it throws an oracle sql error:

Database Error: 511 - ORA-01722: invalid number

Any help is greatly appreciated!

SELECT inventory_view.prtnum, inventory_view.lotnum, inventory_view.expire_dte,     inventory_view.mandte, to_number(inventory_view.expire_dte - inventory_view.mandte) As "DELTA", SUBSTR(inventory_view.age_pflnam, 1, 3) As "Age Profile", to_number((inventory_view.expire_dte - inventory_view.mandte)) - to_number(SUBSTR(inventory_view.age_pflnam, 1, 3)) AS "DELTA 2", lst_arecod, prt_client_id
FROM inventory_view
WHERE inventory_view.prtnum = inventory_view.prtnum
AND lst_arecod <> 'SHIP'
AND prt_client_id = 'TEST'

When you subtract dates, the difference is already a number, so to_number is unnecessary. Apparently, your problem is dependend on the data and appears after the first 99 rows. By modifiying the rownum < limit, you should be able to find the row that causes the problem. My guess is that some values of inventory_view.age_pflnam are not numeric. — Erich Kitzmueller
Thanks for this... Looked at that field to realize I was doing SUBSTR to get the first 3 characters. Well, there are values that only have 1 or 2 values and the third being a dash. This comment lead me right to the solution thanks... I'm now using regular expressions to just grab everything prior to the first occurrence of a hyphen/dash. — user3642066

Joseph B Joseph B · Accepted Answer · 2014-06-27T13:46:27

You're getting the error because one of the to_number conversions fails for a specific row and column. Until you hit the row, you do not get the error, which explains why and rownum < ... runs successfully.

To determine the erring values, you can try the following query, which tests the values of the columns, which are being converted, for non-numeric values.

SELECT 
    inventory_view.prtnum, 
    inventory_view.lotnum, 
    inventory_view.expire_dte,     
    inventory_view.mandte, 
    to_number(inventory_view.expire_dte - inventory_view.mandte) As "DELTA", 
    SUBSTR(inventory_view.age_pflnam, 1, 3) As "Age Profile", 
    to_number((inventory_view.expire_dte - inventory_view.mandte)) - to_number(SUBSTR(inventory_view.age_pflnam, 1, 3)) AS "DELTA 2", 
    lst_arecod, 
    prt_client_id
SELECT inventory_view.*
FROM inventory_view
WHERE lst_arecod <> 'SHIP'
AND prt_client_id = 'TEST'
AND 
(LOWER(inventory_view.expire_dte) <> UPPER(inventory_view.expire_dte)
OR LOWER(inventory_view.mandte) <> UPPER(inventory_view.mandte)
OR LOWER(SUBSTR(inventory_view.age_pflnam, 1, 3)) <> UPPER(SUBSTR(inventory_view.age_pflnam, 1, 3))
);

Also, you can exclude these non-numeric values in your original query, as below:

SELECT 
    inventory_view.prtnum, 
    inventory_view.lotnum, 
    inventory_view.expire_dte,     
    inventory_view.mandte, 
    to_number(inventory_view.expire_dte - inventory_view.mandte) As "DELTA", 
    SUBSTR(inventory_view.age_pflnam, 1, 3) As "Age Profile", 
    to_number((inventory_view.expire_dte - inventory_view.mandte)) - to_number(SUBSTR(inventory_view.age_pflnam, 1, 3)) AS "DELTA 2", 
    lst_arecod, 
    prt_client_id
SELECT inventory_view.*
FROM inventory_view
WHERE lst_arecod <> 'SHIP'
AND prt_client_id = 'TEST'
AND LOWER(inventory_view.expire_dte) = UPPER(inventory_view.expire_dte)
AND LOWER(inventory_view.mandte) = UPPER(inventory_view.mandte)
AND LOWER(SUBSTR(inventory_view.age_pflnam, 1, 3)) = UPPER(SUBSTR(inventory_view.age_pflnam, 1, 3));

Please note that the inventory_view.prtnum = inventory_view.prtnum condition has been removed since it will always be TRUE.

ORA-01722: invalid number - Subtracting Dates

2 Answers