to_number question
- From: Stephen.Lee@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Jul 2004 18:56:06 -0500
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)
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
