Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gB65HNG06009 for ; Thu, 5 Dec 2002 23:17:23 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gB65HM306004 for ; Thu, 5 Dec 2002 23:17:22 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id RAA15966; Thu, 5 Dec 2002 17:56:28 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0051401F; Thu, 05 Dec 2002 17:28:45 -0800 Message-ID: Date: Thu, 05 Dec 2002 17:28:45 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Burton, Laura L." Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Burton, Laura L." Subject: RE: To_Number Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-eb8e518a-c2f5-45a7-b16c-389e2c116767" ------=_NextPartTM-000-eb8e518a-c2f5-45a7-b16c-389e2c116767 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C29CC6.B3DF9FF0" ------_=_NextPart_001_01C29CC6.B3DF9FF0 Content-Type: text/plain Since we don't have that many 3rd party software packages I did make the mistake of asking 'Why??' and received 'Because!' so I too am trying to jump in and 'fix it'. As I responded to another email earlier, the RTrim worked because there were spaces after the amount which was causing the problem. The only problem now is I have one record (so far) that has a unit cost that looks like any other unit cost, yet I receive 'invalid number' for it. The only thing I can figure is that there must be an unprintable character in the field that I cannot see and rtrim is not deleting since it isn't a space. Laura -----Original Message----- [mailto:JApplewhite@austin.isd.tenet.edu] Sent: Thursday, December 05, 2002 5:34 PM To: Multiple recipients of list ORACLE-L Rachel, We have two Third Party apps here for Finance and Student Information that do ridiculous stuff like this so often, I just immediately jumped into "fix it" mode without even questioning. "Don't even ask why" is our motto. ; -) Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) JApplewhite@austin.isd.tenet.edu Rachel Carmichael To: Multiple recipients of list ORACLE-L AHOO.COM> cc: Sent by: Subject: Re: To_Number root@fatcity.c om 12/05/2002 03:49 PM Please respond to ORACLE-L Am I the only one wondering why an obviously numeric field (unit_cost???) is being stored as varchar? --- JApplewhite@austin.isd.tenet.edu wrote: > > Laura, > > Are those really zeros in $34,000.05 or are they letter Os? If so > use > Replace. (Beware of letter l being used instead of numeral 1 as > well.) > > Any leading or trailing spaces? If so use Trim(unit_cost). > > Just a couple of quick suggestions. > > Jack C. Applewhite > > >Burton, Laura > > I have a table which contains a Unit_Cost varchar2(16) which contains > $34,000.05. I can enter select > to_number('$34,990.08','$999,999,999.99') > from dual; and the results is 34990.08. However when I enter select > to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722: > invalid number. > > Is there any other way to do this? I am trying to add a varchar2 > field > that contains $ and commas. I thought the to_number function would > convert > the data to a number field. > > Thanks, > > Laura -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: JApplewhite@austin.isd.tenet.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C29CC6.B3DF9FF0 Content-Type: text/html Content-Transfer-Encoding: quoted-printable RE: To_Number

Since we don't have that many 3rd party software = packages I did make the mistake of asking 'Why??' and received = 'Because!' so I too am trying to jump in and 'fix it'.  As I = responded to another email earlier, the RTrim worked because there were = spaces after the amount which was causing the problem.  The only = problem now is I have one record (so far) that has a unit cost that = looks like any other unit cost, yet I receive 'invalid number' for = it.  The only thing I can figure is that there must be an = unprintable character in the field that I cannot see and rtrim is not = deleting since it isn't a space.

Laura


-----Original Message-----
From: JApplewhite@austin.isd.tenet.edu [mailto:JApplewhite@aust= in.isd.tenet.edu]
Sent: Thursday, December 05, 2002 5:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: To_Number


Rachel,

We have two Third Party apps here for Finance and = Student Information that
do ridiculous stuff like this so often, I just = immediately jumped into "fix
it" mode without even questioning.  = "Don't even ask why" is our motto.    ;
-)

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite@austin.isd.tenet.edu



          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;        
          &nb= sp;         = Rachel           =             =             =             =             =       
          &nb= sp;         = Carmichael           = To:     Multiple recipients of list = ORACLE-L      
          &nb= sp;         = <wisernet100@Y        = <ORACLE-L@fatcity.com>       &n= bsp;           &n= bsp;       
          &nb= sp;         = AHOO.COM>          =   = cc:           &nb= sp;           &nb= sp;           &nb= sp;            =
          &nb= sp;         Sent = by:           &nb= sp; Subject:     Re: = To_Number          &nb= sp;           &nb= sp; 
          &nb= sp;         = root@fatcity.c         &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;            =
          &nb= sp;         = om           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;         
          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;        
          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;        
          &nb= sp;         = 12/05/2002          &n= bsp;           &n= bsp;           &n= bsp;           &n= bsp;           &n= bsp;  
          &nb= sp;         03:49 = PM           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;   
          &nb= sp;         Please = respond           = ;            = ;            = ;            = ;          
          &nb= sp;         to = ORACLE-L          &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p;           &nbs= p; 
          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;        
          &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;           &nb= sp;        


Am I the only one wondering why an obviously numeric = field
(unit_cost???) is being stored as varchar?

--- JApplewhite@austin.isd.tenet.edu wrote:
>
> Laura,
>
> Are those really zeros in $34,000.05 or are = they letter Os?  If so
> use
> Replace.  (Beware of letter l being used = instead of numeral 1 as
> well.)
>
> Any leading or trailing spaces?  If so use = Trim(unit_cost).
>
> Just a couple of quick suggestions.
>
> Jack C. Applewhite
>
>
>Burton, Laura
>
> I have a table which contains a Unit_Cost = varchar2(16) which contains
> $34,000.05.  I can enter select
> = to_number('$34,990.08','$999,999,999.99')
> from dual; and the results is 34990.08.  = However when I enter select
> to_number(unit_cost,'$999,999,999.99') from = elas.qdr I get ora-01722:
> invalid number.
>
> Is there any other way to do this?  I am = trying to add a varchar2
> field
> that contains $ and commas.  I thought the = to_number function would
> convert
> the data to a number field.
>
> Thanks,
>
> Laura



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: JApplewhite@austin.isd.tenet.edu

Fat City Network Services    -- = 858-538-5051 http://www.fatcity.com
San Diego, = California        -- Mailing list = and web hosting services
---------------------------------------------------------------= ------
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of = 'ListGuru') and in
the message BODY, include a line containing: UNSUB = ORACLE-L
(or the name of mailing list you want to be removed = from).  You may
also send the HELP command for other information = (like subscribing).

------_=_NextPart_001_01C29CC6.B3DF9FF0-- ------=_NextPartTM-000-eb8e518a-c2f5-45a7-b16c-389e2c116767-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Burton, Laura L. INET: BurtonL@prismplus.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).