NullToEmpty() & Len() --> Combination !
Posted: Thu Aug 30, 2012 6:15 am
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.
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.