Date value turning into ********** for Nulls

[leon.ho]

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

[DSguru2B]

Welcome Aboard
Simply do a null check using IsNull(). If its coming from a flat file you might also want to check for zero length after trimming the column value and then explicity set it to null using SetNull().

[nick.bond]

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.

[leon.ho]

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

[nick.bond]

is this field (In.CDKFRLU2_CLS_DT) nullable?

[DSguru2B]

I bet not. I think you need to set that as nullable.

[nick.bond]

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.

[leon.ho]

Yes I set the field as Nullable in the Dataset defnition. It is also Nullable in the database.

[DSguru2B]

Make sure its nullable from the first stage.

[nick.bond]

Go further back in the job though. It will probably complain if you do IsNull() on a field that is not nullable.

[leon.ho]

I've set it everywhere except for the actual CFF stage that I used to read the file. It won't let me set the field as Nullable there for some reason. Everywhere else has the field as Nullable = Yes.

[nick.bond]

Try adding $APT_DISABLE_COMBINATION to your job and set it to True for debugging. Run the job and see what it says.

have you tried that? then post the new error message.

[leon.ho]

Ok So I set that environment variable to True and ran the code again. It doesn't look like it gave any additional error messages:



- L

[nick.bond]

Do you have a reject link on the Xfm?? Is there null handling on that column def?

[nick.bond]

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

Code: Select all

IF In.CDKFRLU2_CLS_DT < ' ' THEN SetNull() ELSE In.CDKFRLU2_CLS_DT 

-- edited, got distracted