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