How to replace null to something.

[vsanghvi]

Hi,

I have job where I am reading data from DB2. Now the column I am reading can have null as well. I need to replace that null to "NONE". I did try with CHANGE function but it only works with empty string and not NULL. Is there any inbuilt function or do I need to write custom. Any idea ?

Thanks
V

[logic]

IF THEN ELSE.........

[dls]

You might consider the DB2 function COALESCE.

[Ambuj743]

If IsNull(column_name) Then .. Else column_name

[vsanghvi]

I tried this and it did not like the syntex. For e.g. I wrote If DSLink1.Code = NULL Then DSLink1="NONE" Else DSLink1.Code = "NONE"

Let me now if this is not correct. Or how do i correct it ? Thanks a lot

IF THEN ELSE.........

[Ambuj743]

In the transformer stage use this line e.g I want to put # in the filed which is varchar datatype. if your datatype is integer use 0. It will work.

if isnull(DSLink1.Code ) then '#' else DSLink1.Code

[LogicDude]

You are wrong. How can a NUll be equal to a Null? A Null is never equal to Null. It's not an integer where you can perform mathematical calculation or comparision.

Use what Ambuj said, "IsNull(column_name) Then .. Else column_name"

or you can use, " if nulltozero(column_name) = 0 then ... Else Column_name2"

You can find the "IsNull" function under: Functions --> Null Handling

IHTH

[RaviM]

Hi,

I have job where I am reading data from DB2. Now the column I am reading can have null as well. I need to replace that null to "NONE". I did try with CHANGE function but it only works with empty string and not NULL. Is there any inbuilt function or do I need to write custom. Any idea ?

Thanks
V

Try this :

If (Len(Trim(Link.Columnname))=0 Or IsNull(Trim(Link.Columnname)) Or Trim(Link.Columnname) ="" ) Then "NONE" Else Trim(Link.Columnname)


it will work

[vsanghvi]

Thanks Ambuj743 and others. This works great.

In the transformer stage use this line e.g I want to put # in the filed which is varchar datatype. if your datatype is integer use 0. It will work.

if isnull(DSLink1.Code ) then '#' else DSLink1.Code

[ray.wurlod]

Len(@NULL) returns 0. This may simplify your expression.