Null & Blank

[dssiddu]

hi

can any one tell me the differance between Null & Blank?

And How will we check in DataStage?

For Blank we have to use '' or ""


Adv Thks

[chulett]

What you are calling 'blank' is an 'empty string' and is a known value. Null is the unknown value and is tested for using the IsNull function.

[PhilHibbs]

can any one tell me the differance between Null & Blank?
And How will we check in DataStage?
For Blank we have to use '' or ""

The confusion arises because, by default, DataStage treats a blank value in a Sequential File as a null.

@NULL techincally means "unknown", which is why you lose your information if you concatenate a null field value with a string. Also any comparison (e.g. "DSLink1.Value <> 42" will return @FALSE if Value is a null.

There are two ways around this - either change the properties of your Sequential File stages so that @NULL is represented by a value of your choosing, just live with it. We have so many jobs with so many Sequential File stages that I concluded that the first approach was doomed to failure since someone is bound to forget to set the property correctly, so I wrote a simple transform called CoStr which returns either the first parameter or the second if the first is @NULL, so CoStr(DSLink1.Value,'') returns a blank string if Value is null. Where it matters, we code around the @NULL problem.

[ketfos]

Hi Phil,

Can your share your function of handling nulls in seq file?

Thks
Ketfos

[ray.wurlod]

On the Format tab there's a property in which you can specify how null is represented in the text file. By default this field is empty, which means that "" (a zero-length string) is treated as null. Change it to anything else (such as <NULL>) and you will read "" as "".

[PhilHibbs]

Hi Phil,
Can your share your function of handling nulls in seq file?
Thks
Ketfos

Sure, it's at http://www.hibbs.me.uk/datastage/CoStr.dsx

The "Co" is short for "Coalesce", which is an SQL function that serves a similar purpose, and "Srt" stands for "String", because transforms have to have a return type so I thought I'd start a namespace for "CoInt" etc. which I never needed.