Skip to content
Advertisement

ORA-01722: invalid number – Everything seems a digit

I have the below query:

WITH aux AS (
    SELECT
        col1,
        col2,
        col3
    FROM
        table1
    WHERE
        condition1
)
SELECT
    col1,
    SUM(
        CASE
            WHEN nvl(col3, 9) != '8' THEN
                col2
        END
    ) h,
    SUM(col2) h1
FROM
    aux
GROUP BY
    col1;

However, when I execute it, the below error appears:

ORA-01722: invalid number
ORA-02063: preceding line from RBIP
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number

But, when trying to find what was the entry in col2 that was causing issues, I get no results:

WITH aux AS (
    SELECT
        col2
    FROM
        table1
    WHERE
        condition
)
SELECT
    *
FROM
    aux
WHERE
    NOT REGEXP_LIKE ( col2,
                      '[[:digit:]]' );

Aby hints on how to keep searching for the col2 culprit will be hugely appreciated.

Advertisement

Answer

This expression is not going to find mixed digits and non-digits:

WHERE NOT REGEXP_LIKE ( col2, '[[:digit:]]' );

That is 'AAA1AAA' will match because it has a digit.

Try checking that everything is a digit:

WHERE NOT REGEXP_LIKE ( col2, '^[[:digit:]]*$' );

Or that there are no non-digits:

WHERE REGEXP_LIKE ( col2, '[^[:digit:]]' );
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement