NULL - Informatica

NULL <> 'some Text'   is  NULL,
NULL = 'some Text'  is NULL,
NOT (some Expression with NULL output )   is NULL

NULL  AND (some NULL Expression ) = NULL, NULL  AND (some True Expression ) = NULL,
NULL  AND (some False Expression ) = FALSE,

-------------------------------------------------------------------------------------------------------------

NULL values are treated very differently. You must always explicitly test for them. Both of the following conditions will fail if one or both of the values are NULL: 

LKP_VALUE <> SRC_VALUE 
LKP_VALUE = SRC_VALUE 

NULL is never equal to anything, not even another NULL. NULL is also never NOT equal to anything, not even another NULL. 

The key here is while comparing a null value, Informatica does not evaluate the condition to True or False. It is evaluated to NULL rather. 

There is a setting in Integration Service NullComparion (or something along that line). By default, it is set to NULL you could change this setting to HIGH or LOW. Then could compare null to anything.

Working with Null Values in Boolean Expressions

Expressions that combine a null value with a Boolean expression produces results that are ANSI compliant. For example, the PowerCenter Integration Service produces the following results:

• NULL AND TRUE = NULL
• NULL AND FALSE = FALSE 

Working with Null Values in Comparison Expressions

The following table describes how the PowerCenter Integration Service evaluates the expressions:

Expression	Treat Null in Comparison Operators As
	NULL	HIGH	LOW
NULL > 1	NULL	TRUE	FALSE
NULL = NULL	NULL	TRUE	TRUE

Null Values in Aggregate Functions

The PowerCenter Integration Service treats null values as nulls in aggregate functions. If you pass an entire port or group of null values, the function returns NULL. However, when you configure the PowerCenter Integration Service, you can choose how you want it to handle null values in aggregate functions. You can have the PowerCenter Integration Service treat null values as 0 in aggregate functions or as NULLs.

Null Values in Filter Conditions

If a filter condition evaluates to NULL, the function does not select the record. If the filter condition evaluates to NULL for all records in the selected port, the aggregate function returns NULL (except COUNT, which returns 0). You can use filter conditions with aggregate functions and the CUME, MOVINGAVG, and MOVINGSUM functions.

Nulls with Operators

Any expression that uses operators (except the string operator ||) and contains a null value always evaluates to NULL. For example, the following expression evaluates to NULL:

8 * 10 - NULL 

To test for nulls, use the ISNULL function.

So