NullToEmpty() & Len() --> Combination !

[karthi_gana]

All,

We are using Datastage 8.5 version. I am working on this version for the last 2 months. I have seen/faced many issue with NULL handling stuff in this version.

I just wanted to share something here.

My Code:

NullToEmpty(Lnk1.Code) --> StageVar1
If Len(StagVar1) > 0 Then '1' Else '2' ->StageVar2

All of them are Varchar Datatype only.

The above logic is not giving the expected result.

Sometime Eventhough the Code column contain Null, The output of StageVar is '1'. It should be '2'. Right ?

How this function is working/driven in Datastage 8.5 engine?

If I use

If NullToValue(Lnk1.Code,'') = ''

It is perfectly giving the expected result.

NullToEmpty() = Limitation = ?
NullToValue() = Working Mechanism.

I used the NullToValue() function whenever i faced NULL related issues. When I use NullToEmpty(), Most of the time it is working partially. Say for example, If i have 50 records with NULL value, It may give 30 and the rest of the NULL rows are not properly handled byNullToEmpty() function. This is a strange thing for me. is it a bug in Datstage 8.5? is there any patch needs to be run for this? or am i missing something?

I feel if some experts write an article on "NULL HANDLING MECHANISM ON DATASTAGE 8.5", It would be great for all the developers like me.

[chulett]

is it a bug in Datstage 8.5? is there any patch needs to be run for this? or am i missing something?

Questions for your official support provider and it seems like you've got great information to back up those questions to them.

[SURA]

Hello Karthi_Gana

If you use the Stage variable for that , then stage variable will return TRUE / FALSE only.

So you can check the length in the next level. This is how stage variable will work.

[chulett]

If you use the Stage variable for that , then stage variable will return TRUE / FALSE only.

If you use stage variable for what "that"? I see nothing that will return TRUE/FALSE in what they posted.

[ShaneMuir]

Interesting ...

Does the IsNull() function work correctly?