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." <BurtonL_at_prismplus.com> 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:JApplewhite_at_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_at_austin.isd.tenet.edu
>
>
>
>
>
> Rachel
>
> Carmichael To: Multiple recipients
> of list
> ORACLE-L
> <wisernet100_at_Y <ORACLE-L_at_fatcity.com>
>
> AHOO.COM> cc:
>
> Sent by: Subject: Re: To_Number
>
> root_at_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_at_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_at_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_at_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).
>
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: wisernet100_at_yahoo.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_at_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).
Received on Thu Dec 05 2002 - 20:04:03 CST
