RE: data cleansing question

Lisam

I also would check with the end-users of the data. Marc's points are valid - but if the end-users (or programmers) accessing the data do not have experience with a relational system, they will stumble and grumble about having to learn to use the NVL function. I personally feel that this is a good learning experience for them (drag them screaming and kicking into the relational world). But sometimes, the fight is just not worth it. If this is a small isolated database with few users, then just say screw-it - populate it exactly as it exists on the IBM mainframe.

If you want to educate them in the real-world, then trim the fields to replace the zeros with nulls.

hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, April 17, 2002 8:38 PM
To: Multiple recipients of list ORACLE-L

Hi Marc,

No and no, as far as I know. I should double check on the user requirements, you are right. (It would be helpful if THEY knew)

Thanks, you bring up a good point.

LK

> -----Original Message-----
> From: Marc Perkowitz [SMTP:mperkowitz_at_twjconsulting.com]
> Sent: Wednesday, April 17, 2002 8:04 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: data cleansing question
>
> Do you have mainframe programs that will be brought over to use this
> mainframe data? If so, they will likely have problems with nulls. If
> there
> will only be new programs accessing this data, then it sounds fine.
>
> Will there by any statistical work on these columns? Averages, for
> example,
> will be different with zeroes versus nulls.
>
> Marc Perkowitz
> Senior Consultant
> TWJ Consulting, LLP
>
> 847-256-8866 x15
> www.twjconsulting.com
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, April 17, 2002 5:43 PM
>
>
> > 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: Marc Perkowitz
> INET: mperkowitz_at_twjconsulting.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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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