isnull function

dnat · Post by **dnat** » Mon Apr 04, 2011 11:08 pm

Hi,

I have an oracle stage as input where there are null values in field A.

The next stage is a transformer, and in the stage variable i want to do some validations based on the field A's value.

I use this function

Code: Select all

If isnull(Lnk.A) Then 1 Else 0.

But even if the value is isnull, it always goes to the else condition and executes it.

If i do the same thing in derivations, it is working fine..

What could be the issue.

antonyraj.deva · Post by **antonyraj.deva** » Mon Apr 04, 2011 11:50 pm

If the value is null then, are you to trying to assign a value as '1' for the stage variable and else as '0'?

ray.wurlod · Post by **ray.wurlod** » Tue Apr 05, 2011 12:14 am

Did you build the stage variable expression using the expression editor (selecting operands and operators from menus) or manually?

Incidentally, a sufficient expression would be

Code: Select all

IsNull(Lnk.A)

Boolean expressions return 1 for "true" and 0 for "false".

soumya5891 · Post by **soumya5891** » Tue Apr 05, 2011 10:56 am

Check the value properly,whether the value is null or empty.

jwiles · Post by **jwiles** » Tue Apr 05, 2011 2:28 pm

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,

vivekgadwal · Post by **vivekgadwal** » Tue Apr 05, 2011 2:36 pm

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.

dnat · Post by **dnat** » Wed Apr 06, 2011 1:44 am

Hi,

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.

vivekgadwal · Post by **vivekgadwal** » Wed Apr 06, 2011 7:22 am

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?

soumya5891 · Post by **soumya5891** » Wed Apr 06, 2011 10:27 am

Try with other null handling functions and see it works or not.Convert null to some default value and check that value