To follow up on soumya's comment: within DataStage, an Empty string is not the same as a Null string. An empty string contains no data and has a length of zero...it is not flagged as a NULL. IsNull() will not see an empty string as a null...you must check for the emptiness separately.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
What is the "Nullable" option set to? If it is set as "NO", then you might not be getting nulls during run time in DataStage, even if you can see Nulls in the Database. Also, jwiles and Soumya's comment should also be looked into.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
i manually typed in the code, just to mention that i am using a isnull() function.
When i see the input data through datastage using "View Data", the fields are having values as "NULL". And when i use the same code in derivations, it works. Only in stage variables it is either not considering the input value as a null(even though it is null), or this function has come problem working in a stage variable.
Nullable option is set to "Yes" throughout the job for these fields.
We use the IsNull function extensively, including in stage variables. So, I can confidently say it works. Normally, these kind of scenarios arise by the handling of the data by the tool (w.r.t the Nullable property settings and the data types). Obviously, this is defined by the developer, so it is in our control.
Did you put intermediate files or peek stages and see the data flow at every spot to determine what is going on?
Vivek Gadwal
Experience is what you get when you didn't get what you wanted