Forum Stats

  • 3,734,837 Users
  • 2,247,053 Discussions
  • 7,857,516 Comments

Discussions

Subquery, TO_NUMBER and ORA-01722 invalid number

536323
536323 Member Posts: 9
edited Sep 27, 2006 5:06PM in SQL & PL/SQL
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?

Comments

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    edited Sep 27, 2006 5:06PM
    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
    John Spencer Member Posts: 8,567
    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
This discussion has been closed.