DSXchange

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

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().

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.

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

is this field (In.CDKFRLU2_CLS_DT) nullable?

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

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.

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

Make sure its nullable from the first stage.

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

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.

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.

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

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

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