How to reject junk or unknown values

sathyak098 · Post by **sathyak098** » Fri Jun 21, 2013 2:16 am

Hello,
I have staging table of around 70 columns with the datatype CHAR.
I'm not doing any validation and transformation before load to staging.
So I can expect junk or unknown or unwanted values in almost all the columns.
for eg., Date or integer field may contain alphanumeric values.Varchar field may contain junk values. Because these fields are declared as CHAR in staging.
My requirment is to reject those kind of records before I load from staging to base table. Because Base tables are datatype specific.

May I get your help to achieve this?

miwinter · Post by **miwinter** » Fri Jun 21, 2013 2:38 am

Look at the IsValid function to validate the content against a specified datatype ahead of your load. It's a "type conversion" function class.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 21, 2013 4:57 am

There is no "junk". Every character is part of the data you are being paid to process. The specifications you get from the data owner will tell you how to handle specific characters, even if you have to negotiate those specifications with the data owner. And that specification governs precisely what you must do. Anything not in the specification you do not do.

ArndW · Post by **ArndW** » Fri Jun 21, 2013 5:55 am

Ray put it clearly but I'll paraphrase:
"It's not 'junk' unless the customer tells you it is"

sathyak098 · Post by **sathyak098** » Mon Jul 01, 2013 5:42 am

I was to told to omit the record if it has other than alphanumeric and speacial characters.

chulett · Post by **chulett** » Mon Jul 01, 2013 6:39 am

Do an exact search here for "double convert". You will need to know what "special characters" are allowable to perform this validation.