Laura,
you can try to figure out what is wrong with that one row by using the
dump function to get the octal or hex values of the bytes.
the example from the SQL reference manual:
SELECT DUMP('abc', 1016)
FROM DUAL;
DUMP('ABC',1016)
------------------------------------------
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63
By the way, "because" is only a valid answer if you are a parent
talking to a child. :)
--- "Burton, Laura L." <[EMAIL PROTECTED]> wrote:
> 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:[EMAIL PROTECTED]]
> 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)
> [EMAIL PROTECTED]
>
>
>
>
>
> Rachel
>
> Carmichael To: Multiple recipients
> of list
> ORACLE-L
> <wisernet100@Y <[EMAIL PROTECTED]>
>
> AHOO.COM> cc:
>
> Sent by: Subject: Re: To_Number
>
> [EMAIL PROTECTED]
>
> 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?
>
> --- [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
>
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
