Indicates that a value is either unknown or undefined. NULL is not equivalent to a blank or empty string (for character columns) or 0 (for numerical columns).
Although you can write expressions that return nulls, any column that has the NOT NULL or PRIMARY KEY constraint will not accept nulls. Therefore, if Data Integration tries to write a null value to a column with one of these constraints, the database will reject the row and Data Integration will write it to the reject file. Be sure to consider nulls when you create transformations.
Functions can handle nulls differently. If you pass a null value to a function, it might return 0 or NULL, or it might ignore null values.
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:
•NULL AND TRUE = NULL
•NULL AND FALSE = FALSE
Null values in comparison expressions
When you use a null value in an expression that contains a comparison operator, Data Integration produces a null value. However, you can also configure the Data Integration Server service custom property Treat Null in Comparison Operators As to tell Data Integration to treat null values as high or low in comparison operations.
Use the Treat Null in Comparison Operators As custom DTM property to configure how Data Integration handles null values in comparison expressions. This property affects the behavior of the following comparison operators in expressions:
=, !=, ^=, <>, >, >=, <, <=
For example, consider the following expressions:
NULL > 1 NULL = NULL
The following table describes how Data Integration 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 filter conditions
If a filter condition evaluates to NULL, the function does not select the record.
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: