Subquery, TO_NUMBER and ORA-01722 invalid number

536323

I'm running into a invalid number error (ORA-01722) when I expect none.
I have a table that stores a semester term as a VARCHAR2, all term codes are actually numbers like 200609. There is one exception which I filter out, using a subquery, to prevent the invalid number error.

My query looks like this:
SELECT NVL(SUM(s.balance))
FROM (SELECT s1.term, s1.balance
FROM student_account s1
WHERE s1.student_id = :student_id
AND s1.term <> 'SCRIPT') s
WHERE TO_NUMBER(s.term) <= 200609;

The query errors with ORA-01722.
Now I've checked and rechecked that there isn't another bad term that is not a number.
SELECT DISTINCT '[' || term || ']'
FROM student_account
WHERE student_id = :student_id;

The error complains at line of the WHERE clause.

I've ran just the subquery and changed the column list to select distinct term. All terms are numbers. I think that the outer WHERE clause for some reason still gets the 'SCRIPT' term. Though I've tried to verify this with no luck.

I created a function that simply takes the term writes it to DBMS_OUTPUT and returns it as a VARCHAR2. The weird thing is then the query works. For example the outer WHERE clause becomes: WHERE TO_NUMBER(PRINT_TERM(s.term)) <= 200609

I've also tried to move the TO_NUMBER to the subquery without any luck, same error.

Anyone know what is going on?

JustinCave

Oracle is allowed to push predicates into the subquery, so there is no guarantee that the problem row is filtered out before TO_NUMBER is called. You can get into some very interesting discussions about whether this is the right behavior from a relational theory and/or ANSI standard perspective, but you're probably stuck with the behavior (unless you want to get Oracle to rework their optimizer)

One way to get around the problem would be to write a my_to_number function that catches the error and returns NULL if the to_number conversion fails, i.e.

CREATE FUNCTION my_to_number( p_num IN VARCHAR2 )

  RETURN NUMBER

IS

  l_num number;

BEGIN

  l_num := to_number( p_num );

  RETURN l_num;

EXCEPTION

  WHEN <<Exception whose name escapes me>> THEN

    RETURN NULL;

END;

And use that in your query.

Justin

Message was edited by:
Justin Cave

John Spencer

It is likely that the optimizer is pushing the TO_NUMBER(s.term) <= 200609 predicate into the in-line view and by chance evaluating that before the <> 'SCRIPT'

To avoid the predicate pushing, you can try:

SELECT NVL(SUM(s.balance))

FROM (SELECT s1.term, s1.balance

      FROM student_account s1

      WHERE s1.student_id = :student_id AND 

            s1.term <> 'SCRIPT' and

            ROWNUM > 0) s

WHERE TO_NUMBER(s.term) <= 200609;

HTH
John