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:]]' );