IsNull not working

[mac4rfree85]

Hi Guys,

I have a field coming with NULL values. Since i am working in Server job, i was not able to use NulltoEmpty() function.

So, i wrote a if then else clause, but it is not working. Iam still able to see NULL values in the column.

Code: Select all

If IsNull(trim(columnName)) then '' else ColumnName

I even tried " instead of ' but it too ended up showing Null Value. I even tried the below code but of no use.

Code: Select all

If trim(columnName)=@NULL then '' else ColumnName

Can somebody guide me as what i am doing wrong here.

Thanks for your help in advance..Cheers!!!!!

[MT]

Hi mac4rfree85,

why do you want to trim something which is NULL?
Try this logic

If IsNull(columnName) Then "" Else trim(columnName)

regards
Michael

[mac4rfree85]

only some rows are null, some of them are having values with them.. i jus want to convert the ones which are coming as null to empty..

[chulett]

Since i am working in Server job, i was not able to use NulltoEmpty() function.

Of course you can use it... but it will do exactly what you've already done. If it is "not working" then you either don't have nulls, are confusing an empty string with a null or have an Oracle target where your empty string gets automatically converted to a null.

[ray.wurlod]

You need to be confident that you have NULL coming in. "" is not the same as null. Is the source a sequential file? If so, how is NULL mapped?

[soumya5891]

Whenever you extract that field from the database use any null handling function like COALESCE function because it may happen that database null does not match with data stage null

[ray.wurlod]

Whenever you extract that field from the database use any null handling function like COALESCE function because it may happen that database null does not match with data stage null

Do you have any proof of this? Over the last 13 years I've found DataStage null handling to be quite reliable, no matter what database was involved.

[mac4rfree85]

i am pulling data from SAP table and loading it to Oracle stage.. In SAP table, when i check, these columns are NULL and i am not doing any transformation.. Its a direct pull but when i check the the Oracle stage, instead of empty string am getting only Null Value. ..

[ray.wurlod]

But that's correct! If you're pulling NULL from SAP and inserting untransformed data into Oracle, then you should expect to see NULL in Oracle.

You can't trim NULL (at least not till version 8.5). To detect NULL, therefore, you need to apply the IsNull() function to the original input column, as MT suggested. To reiterate, if you want to transform the NULLs into zero-length strings:

Code: Select all

If IsNull(inLink.ColumnName) Then "" Else Trim(inLink.ColumnName)

[chulett]

... and even then Oracle will turn the empty string back into a NULL when you load it, from what I recall and noted above. Is there some problem with a null in your source being a null in your target as well?