| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> to_number question
OK folks. I've slammed this around every which way I can think of.
Two columns of a table are char(18) and contain 13, 15, or 16 numeric
digits. The digits are KNOWN to be strictly numeric (except for the padding
out to 18 characters).
I can select:
to_number(COLUMN) OK
to_number(trim(COLUMN) OK
cast(COLUMN AS NUMBER) OK.
to_number(COLUMN A) - to_number(COLUMN B) OK
to_number(COLUMN A) - 12345 OK
But if I try something like
select yadayada from table where 123 < {any of the above number conversions)
I get ORA-01722: invalid number
with the * under the number conversion in the where clause.
Generally, if I try ANY kind of comparison or arithmetic operation in the
WHERE clause, I get the error.
While I can: select to_number(COLUMN A) - to_number(COLUMN B) OK
I cannot do: WHERE to_number(COLUMN A) - to_number(COLUMN B) > 0;
I've tried fiddling with format specifiers in the TO_NUMBER function. The TO_NUMBER section of the O'Reilly PL/SQL Programming sheds no light on the problem. Might there be someone amongst the learned out there who can? (The version is 9.2.0.4)
 |
 |