NULL value handling in Transformer

[forboy]

Yes,

Columns must be checked for isNull function before checking to another value. I did many complex calculations in the transformer and experienced the same thing.

One more thing null values can not be assigned to the stage variables in PX.

forboy

[trobinson]

Most code is sensitive about NULLS. A NULL means "NOTHING". You cannot compare something to nothing therefore an IF will not work on a NULL value in a field. This is why we have NULL Processing functions.

[Ultramundane]

You can always compare a set of something to a set of nothing. What is tough is comparing a set of something to a set of something that is unknown. That will throw ya.

A set of something other than nothing is never equal to the set of nothing. They are mathematically different.

Anyways, in relational databases a NULL does not mean nothing. It means unknown. In relation databases, a zero or zero length string typically means nothing.

I wish that datastage operated like most Enterprise class database systems any other system for that matter that follows ANSI-92 standards for NULL handling.

I believe they are:
1. When a NULL is encountered in a calculation and it is not mapped to something known with ISNULL, NULLIF, IFNULL that the calculation should return NULL.

2. Booleans checks can only be stepped into if the condition/state evaluates as true. With NULLS they are are 3 states.
TRUE, FALSE, UNKNOWN.

Use #1 as well as these rules to determine states.
NOT(TRUE) = FALSE
NOT(FALSE) = TRUE
NOT(UNKNOWN) = UNKNOWN

TRUE AND FALSE = FALSE
TRUE AND UNKNOWN = UNKNOWN
FALSE AND UNKNOWN = FALSE
FALSE AND FALSE = FALSE
UNKNOWN AND UNKNOWN = UNKNOWN

TRUE OR FALSE = TRUE
TRUE OR UNKNOWN = TRUE
FALSE OR UNKNOWN = UNKNOWN
FALSE OR FALSE = FALSE
UNKNOWN OR UNKNOWN = UNKNOWN