Re: To_Number

On Friday 06 December 2002 08:29, Mercadante, Thomas F wrote:
>
> I think their theory was to keep the JD Edwards system totally RDBMS
> neutral - they could switch out any backend that they wanted to.

A patently ridiculous idea.

Jared

>
> Tom Mercadante
> Oracle Certified Professional
>
>
> -----Original Message-----
> Sent: Friday, December 06, 2002 10:34 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> That's probably why it's in a VARCHAR.
>
> "Correct the data"? What strange ideas you mortals have.
>
>
>
>
>
> "Burton,
>
> Laura L." To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> <BurtonL cc:
>
> @prismplus.co Subject: RE: To_Number
>
> m>
>
> Sent by: root
>
>
>
>
>
> 12/06/2002
>
> 09:44 AM
>
> Please
>
> respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> Thanks for the email!! I did not know about Dump so I learned something
> new. The record I thought I had a problem with was ok and that is why I
> did not see anything amiss. However the record after this one was
> $20041-94. Corrected that and got $145.34 EA. I just told the developer
> to handle the 1722 error and let someone else deal with how to correct the
> data. It's ridiculous!
>
>
> Laura
>
>
>
>
>
> -----Original Message-----
> Sent: Thursday, December 05, 2002 8:29 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
>
>
> Did you check it with dump() ?
>
>
>
>
>
> select dump(unit_cost,16) from tablewhatever;
>
>
> for Hex. Check for unprintables that way. Try inserting the bad values
> into a look-aside table for later analysis i.e.
>
>
> create table look_aside (rownum rowid, bad_val varchar2(20),
> dump_val varchar2(200));
>
>
> declare
> bad_num exception;
> numval number(10,2);
> pragma exception_init(bad_num,-1722);
> Cursor C1 is select rowid,unit_cost from yourtable;
> begin
> for x in C1
> loop
> declare -- inner block will allow exception trap
> begin
> -- trim and change o's to zeroes
> numval := to_number(rtrim(replace(x.unit_cost,'O','0')));
> exception
> when bad_num
> then
> insert into look_aside values (x.rowid,x.unit_cost,
> substr(dump(unit_cost,16),1,200));
> end;
> end loop;
> commit;
> exception
> when others
> then
> dbms_output.put_line(sqlerrm);
> end;
> /
>
>
>
>
>
> You can use the rowids in the look-aside table to zap
> the bad values later.
>
>
> HTH
>
>
> Jeff Herrick
>
> On Thu, 5 Dec 2002, Burton, Laura L. 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
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jeff Herrick
> INET: jherrick_at_igs.net
>
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).