How to check for nulls in transformation

[mmkhan]

Hi folks
In my transformation i have a field which has to checked for nulls.
Something like this

Code: Select all

if DSLink.field=null then ' ' else DSLink.field

When i use this it gives me an error.
So i tried this

Code: Select all

if TRIM(DSLink.field)='' then ' ' else DSLink.field

but what will happen for TRIM(null) what will TRIM return for a null value.

Thanks In Advance

[chulett]

Without going into tons of detail, there is an "IsNull" logical function so you could try using:

Code: Select all

if IsNull(DSLink.field) then ' ' else DSLink.field 

Nothing is ever "equal to" null.

[ray.wurlod]

NULL is "unknown" value. Pretty much any operation that you perform on NULL returns NULL. So, if you try to ask "is this equal to something whose value you don't know", it can only answer "don't know".

There is one other way to do what you're interested in doing, a technique called a "substitution conversion".
Oconv(arg1,"S;x;y;z") returns x if the value of arg1 (in a Boolean context) is "true", y if the value of arg1 is "false", and z if the value of arg1 is NULL. An asterisk means "leave the value of arg1 unchanged".
In your example, you could use

Code: Select all

Oconv(DSLink.field, "S;*;*;' '")

[vzmz]

Without going into tons of detail, there is an "IsNull" logical function so you could try using:

Code: Select all

if IsNull(DSLink.field) then ' ' else DSLink.field 

Nothing is ever "equal to" null.

Thanks
That was a valueable information

[vmcburney]

I usually use a DataStage routine which is a version of the Oracle NVL command. DSNVL(DSLink.field, ' ') where the first argument is the value to be checked and the second is the replacement value if the first value is null. You may want to put zeros into numeric fields and spaces into text fields.

FUNCTION DSNVL(Arg1, Arg2)
if isnull(Arg1) then
Ans = Arg2
end else
Ans = Arg1
end

This gives you a shorthand version and simplifies transformer code, though Ray's suggestion is something I'll keep in mind for boolean fields.

[chulett]

There is one other way to do what you're interested in doing, a technique called a "substitution conversion".
Oconv(arg1,"S;x;y;z") returns x if the value of arg1 (in a Boolean context) is "true", y if the value of arg1 is "false", and z if the value of arg1 is NULL. An asterisk means "leave the value of arg1 unchanged".

Well, that's a new one on me... interesting. Sorta like an Oracle 'decode' specifically for boolean values. Have to add that to my list.