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