Null Error checking for nulls
Posted: 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.
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.