Null Error checking for nulls

rmcclure · Post by **rmcclure** » Tue Jun 03, 2014 8:36 am

Hello,

I am having a problem checking for Nulls in a transformer stage using Datastage 8.1

I have a field called FieldOne that comes in from the source.

I have a stage variable (svRejFieldOne) defined to create a flag if FieldOne is Not Null AND the length is not 6 or 7:
If IsNotNull(inFromSrc.FieldOne) Then If (Len(inFromSrc.FieldOne) < 6 OR Len(inFromSrc.FieldOne) > 7) Then 1 Else 0 Else 0

The IsNotNull function works fine in the stage variable

But..
In my output link for any non rejected rows I have:
If IsNotNull(inFromSrc.FieldOne) Then UpCase(inFromSrc.FieldOne) Else inFromSrc.FieldOne

When I run this I get an error:
APT_CombinedOperatorController,0: Field 'FieldOne' from input dataset '0' is NULL. Record dropped.

As a test I did:
If IsNotNull(inFromSrc.FieldOne) Then inFromSrc.FieldOne Else inFromSrc.FieldOne
and
If IsNull(inFromSrc.FieldOne) Then inFromSrc.FieldOne Else inFromSrc.FieldOne

In both cases I get the same error. If I remove the IsNotNull function and pass FieldOne as-is to the output if works fine.
Why would the function work fine in the stage variable but not in the output link? Why would the IsNull function fail because the value is Null?

Thanks in advance.

ArndW · Post by **ArndW** » Tue Jun 03, 2014 9:54 am

The first thing I would do is disable operator combination in a test copy of this job, just to ensure that the error is actually occurring where you think it is occurring (you might be surprised in this case, since the code you posted looks OK). Recompile and re-run and see what the new error message is.

ssnegi · Post by **ssnegi** » Tue Jun 03, 2014 7:04 pm

create a stage variable :
fone : Trim(inFromSrc.FieldOne)
Output Derivation :
If inFromSrc.FieldOne <> '' Then If (Len(fone)) < 6 OR len(fone)) > 7) Then 1 Else 0 Else 0

rmcclure · Post by **rmcclure** » Fri Jun 06, 2014 8:05 am

Thanks for the tips. The problem is solved: I noticed the input field was a nvarchar and the output field was a varchar. I changed the imput field to varchar and it works. Not sure why....but it works.

rkashyap · Post by **rkashyap** » Fri Jun 06, 2014 12:41 pm

We have had our experiences with "unexpected" results from Null handling operators in version 8.1. Our work around was to check for nulls using "Len(Variable)=0".