Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: data cleansing question

RE: data cleansing question

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Mon, 22 Apr 2002 06:13:25 -0800
Message-ID: <F001.0044AD7E.20020422061325@fatcity.com>


Hi Stefan,

The EBCDIC->ASCII conversion is handled on the mainframe for me. I am sorry I don't know much about the mainframe environment here, I want to say it's VSAM, there definately is no database. It is so old, it's the type of mainframe where everything is on TAPE.

Oracle version: 8.1.7.3 on Windows 2000 sp2

HTH Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
Ft. Lauderdale, FL, USA

> -----Original Message-----
> From: Stefan Jahnke [SMTP:Stefan.Jahnke_at_bov.de]
> Sent: Monday, April 22, 2002 4:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: AW: data cleansing question
>
> Hi
>
> I've got a question regarding your migration host -> oracle, since I'll
> have
> the same
> problem in the near future.
> How do you deal with the EBCDIC to ASCII problem ?
> Do you migrate from VSAM or DB2 ? Which version of Oracle are you on ?
>
> Regards,
> Stefan
>
> -----Ursprüngliche Nachricht-----
> Von: Koivu, Lisa [mailto:lisa.koivu_at_efairfield.com]
> Gesendet: Sonntag, 21. April 2002 12:48
> An: Multiple recipients of list ORACLE-L
> Betreff: RE: data cleansing question
>
>
> Hi Yechiel,
>
> I'm aware of the NULLIF clause. However my strategy has been to bring the
> entire file in, character based, so I can take a look at the data prior to
> modifying it. I'm using NULLIF <column_name> is equal to spaces.
>
> I made an even better decision - the users decided they didn't want all
> these fields, so suddenly my loads scream and my table is much smaller.
> <whew>
>
> It would be nice if I knew exactly what the SQL against the table would
> be.
> Unfortunately the users don't really know either. (Say it over and over
> again: I love my job, I love my job...)
>
> Lisa Koivu
> Oracle Database Administrator
> Fairfield Resorts, Inc.
> Ft. Lauderdale, FL, USA
>
>
> > -----Original Message-----
> > From: Yechiel Adar [SMTP:adaryechiel_at_hotmail.com]
> > Sent: Sunday, April 21, 2002 5:33 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: data cleansing question
> >
> > Hello
> >
> > There are a few considerations:
> >
> > 1) If you import the data using the loader you can use the NULLIF clause
> > so it will import all zero field as null.
> > 2) Check carefully for usage of the fields in the WHERE part of the SQL.
> > Putting nulls instead of zero can cause the users to do outer joins.
> >
> > Yechiel Adar
> > Mehish
> >
> > ----- Original Message -----
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, April 18, 2002 12:43 AM
> >
> >
> > > For those of you who have had to deal with data cleansing -
> > >
> > > I am working on importing mainframe data into Oracle. This
> unglamorous
> > job
> > > involves validating (and sometimes compensating for) what is bad data
> in
> > > Oracle's eyes but not on the mainframe - crazy crap like a date =
> > > 22/22/2022. I also find that the mainframe programs are padding null
> > fields
> > > with 0's.
> > >
> > > I wonder how many of you take the route of removing the zeros and
> > storing
> > > null in that field? Some of the important numeric fields I think I'll
> > leave
> > > that way (past_due_amt, etc.) but several others in this 218-field
> table
> > are
> > > full of zeroes. My gut feel is to null out the insignificant 0'd out
> > fields
> > > - that I have pushed to the bottom of the table - to not only save
> > space,
> > > but for data integrity. 0 in 75 fields means nothing to me or to
> anyone
> > > else, as far as I can tell.
> > >
> > > Thoughts?
> > >
> > > Thanks everyone
> > >
> > > Lisa Koivu
> > > Oracle Database Hormone Dispenser.
> > > Fairfield Resorts, Inc.
> > > 954-935-4117
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Koivu, Lisa
> > > INET: lisa.koivu_at_efairfield.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > 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: Yechiel Adar
> > INET: adaryechiel_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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: Koivu, Lisa
> INET: lisa.koivu_at_efairfield.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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: Stefan Jahnke
> INET: Stefan.Jahnke_at_bov.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Mon Apr 22 2002 - 09:13:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US