Following is some basic Info about the behavior of informatica PowerCenter regarding default and NULL values.
Starting with default values, there is a major distinction on how Informatica PowerCenter treats expression ports versus mapping variables/parameters. Expression ports with no value are being assigned Null values during initialization, where mapping variables are being given a default value depending on their datatype.
More specifically, Ports per datatype
And default values assigned to variables per datatype
Starting with default values, there is a major distinction on how Informatica PowerCenter treats expression ports versus mapping variables/parameters. Expression ports with no value are being assigned Null values during initialization, where mapping variables are being given a default value depending on their datatype.
More specifically, Ports per datatype
And default values assigned to variables per datatype
It might seem basic but you can avoid many well hidden bugs that more than often occur based on that trivial information.
Another caveat regarding Null values and PowerCenter is the difference in behavior between"DECODE" and "IIF" expressions when evaluating NULL operands.
"IIF" expressions return FALSE, i.e:
IIF(NULL=NULL, 1, 2)
returns 2.
On the other hand "DECODE" evaluates Null operands as equal, i.e:
DECODE(NULL, NULL, 1, 2)
returns 1.
That seems different to a common RDBMS's decode expression, since SQL returns false on nulls decode i.e:
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 2 END
Returns 2
Another caveat regarding Null values and PowerCenter is the difference in behavior between"DECODE" and "IIF" expressions when evaluating NULL operands.
"IIF" expressions return FALSE, i.e:
IIF(NULL=NULL, 1, 2)
returns 2.
On the other hand "DECODE" evaluates Null operands as equal, i.e:
DECODE(NULL, NULL, 1, 2)
returns 1.
That seems different to a common RDBMS's decode expression, since SQL returns false on nulls decode i.e:
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 2 END
Returns 2
The above behavior can be customized by fiddling with the TreatNullInComparisonOperatorsAs at the corresponding integration service properties.
The aforementioned option change the behavior of sorters and aggregators as well. It is best advised to take care of this behavior on the mapping itself and not depend on server specific settings that can be different between environments.
The aforementioned option change the behavior of sorters and aggregators as well. It is best advised to take care of this behavior on the mapping itself and not depend on server specific settings that can be different between environments.
