RE: To_Number

Yeah. I was doing data loads for a JD Edwards system (migration from a legacy to JD actually). we had to move the decimal point for some number values from their original to some ridiculously high value to get the data to look right within the system. talk about a waste of money (buying Oracle for this product).

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, December 06, 2002 7:24 PM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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).