RE: to_number question

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 14 Jul 2004 19:03:28 -0600
Message-Id: <6.1.0.6.2.20040714190225.02dc83a0@pop.centrexcc.com>

It will also fail in the select:

19:00:40 ora92.scott> select * from lee;

459100154152407
1672324589227
75534921326846
895924377396152
619950297203501
890345901043545
519381709242400
404986490556978
741360347483576
706947665719106
702244196248142
364997585414739
375588978751301
500404215742764
220236779017578
401577792893321
718130569761368
907064124732247
409003793863253
557235 557235
300757076,300757

21 rows selected.

19:00:51 ora92.scott> col n1 for 999,999,999,999,999,999 19:01:35 ora92.scott> select to_number(c1) n1 from lee;

                       N1
------------------------
      459,100,154,152,407
        1,672,324,589,227
       75,534,921,326,846
      895,924,377,396,152
      619,950,297,203,501
      890,345,901,043,545
      519,381,709,242,400
      404,986,490,556,978
      741,360,347,483,576
      706,947,665,719,106
      702,244,196,248,142
      364,997,585,414,739
      375,588,978,751,301
      500,404,215,742,764
      220,236,779,017,578

ERROR:
ORA-01722: invalid number

At 06:38 PM 7/14/2004, you wrote:
> > -----Original Message-----
> > Almost certainly somewhere in your column you have at least
> > one value that fails to convert to a number.
>
>I'm inclined to agree because the following works:
>to_number(translate(nbr_cc_no_high,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcd
>efghijklmnopqrstuvwxyz ','0123456789'))
>
>Some creator of data around here needs to be slapped.
>
>You still have to wonder why it isn't a problem in the SELECT part; but IS a
>problem in WHERE part.
>
>----------------------------------------------------------------
>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 - 20:00:28 CDT