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)
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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
