Is there a better way to handle nulls ???

cosec · Post by **cosec** » Fri Jun 08, 2007 8:52 am

Loading data from a fixed with Sequential File to a DB2 Table. Some of the records I load consists of null columns...they are of data type date, timestamp,char,integer,decimal

for date and timestamp I use a validation for the null handling....

For the others data types if I dont validate I get warnings.....

Is it necessary for me to validate the columns with other data types for nulls...isn't there a simpler way to perform this....?

Thanks

srimitta · Post by **srimitta** » Fri Jun 08, 2007 11:47 am

Simple and better way is handle Null's coming from source column's explicitly:
1). When Tagret column is defined as Not Null irresptive of data types.
2). When Target column is defined as Numeric, Integer, Decimal, Small Int, Float..........other data types which are defined to accept only Numeric values even if the column is set accept Null values.

DSguru2B · Post by **DSguru2B** » Fri Jun 08, 2007 1:02 pm

If it is a fixed width file, you need to check for each and every one. As there are no nulls in a fixed width file. They are all character fields.

gateleys · Post by **gateleys** » Fri Jun 08, 2007 2:18 pm

cosec wrote: Is it necessary for me to validate the columns with other data types for nulls...isn't there a simpler way to perform this....?

Yeah it is necessary if your target requires that column to be not null.

And, what's so complex about handling nulls?

gateleys[/b]

ray.wurlod · Post by **ray.wurlod** » Fri Jun 08, 2007 4:16 pm

Note, too, that the representation of NULL in the Columns grid must have the same number of characters as the width of the field to which it relates.

kommven · Post by **kommven** » Fri Jun 08, 2007 5:18 pm

A typical scenario with Fixed width flat file will always represent NULL as spaces equal to the field length.

DataStage will never consider space as a valid representation for NULL.

I will do a Length after a Trim Validation to find out if its a NULL representation for a field or not.

Code: Select all

If Len(Trim(InputLink.InputColumn)) = 0 Then SetNull() Else InputLink.InputColumn

But using this feature should be considered conciously and assumed as a data representation with spaces will be lost during this transformation.

Another approach is if all the available characters in that data are spaces then thats a NULL {Can be achieved by writing a Routine maybe} --

-K

ray.wurlod · Post by **ray.wurlod** » Fri Jun 08, 2007 7:07 pm

Can be achieved in the metadata.

That's the point I've been making.

cosec · Post by **cosec** » Fri Jun 08, 2007 10:07 pm

Thanks for all your responses....I guess I have no choice but to handle all the columns for nulls.....I was looking for a simpler solution as I have an enormous number of jobs to alter....thanks again...

chulett · Post by **chulett** » Sat Jun 09, 2007 7:26 am

Well... yes, you have to determine which fields need to have null handling. Note that that is a blanket statement regardless of source or situation - one of your jobs as a developer is to determine if nulls will wreak havoc on your process and handle them properly. Always.

While we'd all love to have a 'simpler solution' handed to us for everything, that's not always possible. Besides, this isn't exactly a complicated solution that you need in this case - what sucks for you is the fact that it wasn't done originally and now you have to retrofit this into an 'enormous number' of jobs. Look on the bright side - it's a great object lesson.

Involve others. Share the pain!

And in this particular case, Ray has given you about the simplest solution of all - but I get the impression you keep overlooking it.

ray.wurlod · Post by **ray.wurlod** » Sat Jun 09, 2007 6:33 pm

Could you identify the rotten apples in a barrel without inspecting every one?

DSXchange

Is there a better way to handle nulls ???

Is there a better way to handle nulls ???

Re: Is there a better way to handle nulls ???