Transformer Error

[Shruthi]

Hi,

When does an error like this occur.

APT_CombinedOperatorController(0),0: Field 'ColName' from input dataset '0' is NULL.

I have the following condition in a Timestamp field of Transformer.

If ColName='00000000' Or StripWhiteSpace(ColName)='' Then '' Else StringToTimestamp(ColName,"%yyyy%mm%dd")

The input is ColName varchar(8) in %yyyy%mm%dd format and the output is Timestamp datatype. the input and output column are same and nullability is set to Yes.

By some means, a record is getting rejected.

Any thoughts on this?

[jwiles]

Because ColName is nullable, you must include "null handling" logic in your transformer derivations. Most functions within transformer do not natively handle null values and when they encounter a null the record will be rejected.

IsNull(), IsNotNull(), NullToValue(), NullToEmpty(), setNull() are the null-handling functions within transformer. For your example logic, this would be appropriate:

If IsNull(ColName) then '' else if ColName='00000000' or StripWhiteSpace(ColName) = '' then '' else StringToTimestamp(ColName,"%yyyy%mm%dd")

Code IsNull() and IsNotNull() as separate conditions in your if-then-else logic. "If IsNull(ColName) or ColName='00000000'" will force a reject if ColName is null due to how the if conditions are evaluated.

Regards,

[Shruthi]

Thanks James. It worked! :D