Re: to_number question

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 14 Jul 2004 18:31:30 -0600
Message-Id: <6.1.0.6.2.20040714182510.02dde890@pop.centrexcc.com>

I don't know. Works for me:

18:20:16 ora92.scott> create table lee(c1 char(18));

Table created.

18:20:31 ora92.scott> insert into lee

18:20:49   2  select to_char(trunc(100000000000000000*dbms_random.value))

from dba_objects where rownum < 20;

19 rows created.

18:22:30 ora92.scott> insert into lee
18:23:15 ora92.scott> edit
Wrote file afiedt.buf

1 insert into lee
2* select to_char(trunc(1000000000000000*dbms_random.value)) from dba_objects where rownum < 20
18:23:24 ora92.scott> /

19 rows created.

18:23:25 ora92.scott> commit;

Commit complete.

18:23:30 ora92.scott>
18:23:30 ora92.scott>
18:23:30 ora92.scott>
18:23:30 ora92.scott> select * from lee;

54766202608072298
47714366207543606
94569763245502721
9704033027245524
30075707624387393
25835838422837782
49326672363439124
55723539338499250
67216143916399660
17973108250347286
36531817837894777
87547102610307264
74498150285529373
84273519819194628
60247615092860194
66398529130692431
26607418570418486
13318229568393119
55601756874584917
914162187484571
18620444744061
253582567485304
845030140465792
934049530017801
359402098651524
931860131787817
406655914675934
803577557027722
151824897606360
235860679952926
332264921785444
981363809879106
201662188742529
203481991325558
2894603599783
196445723255970
177208831301888
554036099791080

38 rows selected.

18:23:30 ora92.scott>
18:23:30 ora92.scott> select c1 from lee where 914162187484571 < to_number(c1);

22 rows selected.

18:24:20 ora92.scott>

Are you really absolutely positively sure that all columns have only numeric characters. No special characters, or imbedded blanks.

At 05:56 PM 7/14/2004, you wrote:

>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_at_freelists.org
>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
>-----------------------------------------------------------------

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org 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
-----------------------------------------------------------------

Received on Wed Jul 14 2004 - 19:28:45 CDT