Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Subquery, TO_NUMBER and ORA-01722 invalid number

536323Sep 27 2006 — edited Sep 27 2006
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

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2006
Added on Sep 27 2006
2 comments
2,185 views