Date value turning into ********** for Nulls
Moderators: chulett, rschirm, roy
Date value turning into ********** for Nulls
Hi,
I'm reading a Complex Flat File which as a date field that sometimes contains NULL value. I use a Split Subrecord stage to flatten the file and then map this date field to output defined as date. When the incoming value is NULL the output turns into **********, which causes a problem when I try to load the value into the target database (I get 4713/01/01 as the date using Teradata Multiload stage). How can I handle this field so that it would output NULLs into the target table?
Thanks,
- Leon
I'm reading a Complex Flat File which as a date field that sometimes contains NULL value. I use a Split Subrecord stage to flatten the file and then map this date field to output defined as date. When the incoming value is NULL the output turns into **********, which causes a problem when I try to load the value into the target database (I get 4713/01/01 as the date using Teradata Multiload stage). How can I handle this field so that it would output NULLs into the target table?
Thanks,
- Leon
watch out for fields that are set to 'not nullable' from the output of certain stages (certainly lookups). DS will fill that field with Ascii nulls which will cause you a problem when loading, and will not be identified with IsNul() or Len(Trim(field)) = 0. Change the field to nullable and hopefully a null will be used instead of Ascii null.
If that still doesn't work you may need to check for field < ' ', i.e. checking if the field contains characters that would sort less than a space, which is the lowest printable Ascii character.
If that still doesn't work you may need to check for field < ' ', i.e. checking if the field contains characters that would sort less than a space, which is the lowest printable Ascii character.
Regards,
Nick.
Nick.
Hi,
Thanks for the quick reply. I'm doing currently doing this and landing it into a dataset.
IF IsNULL(In.CDKFRLU2_CLS_DT) THEN SetNull() ELSE In.CDKFRLU2_CLS_DT
It is giving me the following warnings:
xfmBuild_RollP_Fmt: When checking operator: When binding input interface field "input0Int8CDKFRLU2_CLS_DT0" to field "CDKFRLU2_CLS_DT": Using "null" conversion with non-nullable input
Rejects: When checking operator: When validating export schema: At field "CDKFRLU2_CLS_DT": Exporting nullable field without null handling properties
and the job aborts...
APT_CombinedOperatorController,0: Internal Error: (srcNull): api\interface_rep.C: 6352
... As far as I can tell I can't set any properties in the dataset stage to handle nulls except set Nullable to "Yes". I'm really not sure what to do here...
- Leon
Thanks for the quick reply. I'm doing currently doing this and landing it into a dataset.
IF IsNULL(In.CDKFRLU2_CLS_DT) THEN SetNull() ELSE In.CDKFRLU2_CLS_DT
It is giving me the following warnings:
xfmBuild_RollP_Fmt: When checking operator: When binding input interface field "input0Int8CDKFRLU2_CLS_DT0" to field "CDKFRLU2_CLS_DT": Using "null" conversion with non-nullable input
Rejects: When checking operator: When validating export schema: At field "CDKFRLU2_CLS_DT": Exporting nullable field without null handling properties
and the job aborts...
APT_CombinedOperatorController,0: Internal Error: (srcNull): api\interface_rep.C: 6352
... As far as I can tell I can't set any properties in the dataset stage to handle nulls except set Nullable to "Yes". I'm really not sure what to do here...
- Leon
I think this might be complaining about the mapping from the previous stage. It looks like the operators have been combined so maybe the error message is a bit misleading
where is this field? input0Int8CDKFRLU2_CLS_DT0
Try adding $APT_DISABLE_COMBINATION to your job and set it to True for debugging. Run the job and see what it says.
where is this field? input0Int8CDKFRLU2_CLS_DT0
Try adding $APT_DISABLE_COMBINATION to your job and set it to True for debugging. Run the job and see what it says.
Regards,
Nick.
Nick.
I'm pretty sure it's just complaining about the IsNull() on that field when it thinks it can't contain a null as it is not nullable.
What happend if you change it to
-- edited, got distracted
What happend if you change it to
Code: Select all
IF In.CDKFRLU2_CLS_DT < ' ' THEN SetNull() ELSE In.CDKFRLU2_CLS_DT
Last edited by nick.bond on Fri Apr 27, 2007 10:26 am, edited 1 time in total.
Regards,
Nick.
Nick.