Null records got dropped

[ashishm]

Hi,

I'm doing a full outer join and a transformer stage after it to send the unmatched records to different files. But in the result, the unmatched records are just dropped in the transformer stage instead of going through the link where it is suppose to go.

here is the error message:
TRNS_MergeAcctAcctCust,0: Field 'rightRec_APPLID' from input dataset '0' is NULL. Record dropped.


I set all fields to Nullable, so I'm wondering why they could still be dropped. Thanks in advance.

[BI-RMA]

Hi ashishm,

do you use this input-column anywhere in a transformer-function?

These do not accept NULL-Values.

[ashishm]

Hi,

Thanks for the reply, I don't use any functions in the join stage or the transformer stage, could you give me a little bit more detail about this input-column? Thank you.

[BI-RMA]

I was just guessing for one possible reason your records get dropped. If a column used as input for a transformer-function shows a NULL-value this is what happens.

The same is true, if you use the column as input for a StageVariable so that the Return-Value of the StageVariable gets NULL (from 8.5 onwards Null-handling in StageVariables is different).

One last possibility is that your target enforces NOT NULL-Constraints even though You specify the respective columns as nullable in the job (for example a database table --> Metadata conflict). I would expect the warnings in the job log to be different though, since they would be issued by the database server.

[jwiles]

Basically if you do anything other than a straight copy to output with a nullable input column in a transformer, you should include some sort of null handling logic. As mentioned, this could be in a stage variable derivation or a function, but also simple if-then-else logic, constraints and so on. Any logic where the transformer will need to access/examine the data stored in the column.

Regards,