|Bookshelf Home | Contents | Index | PDF|
NULL in SQL represents a value that is not known or is not applicable. Expression evaluation with NULL is somewhat different than with other values. Since NULL is not a value, comparison functions do not operate normally when one or both of the operands are NULL. For instance, NULL = NULL is not TRUE.
SQL and Siebel applications provide special functions and grammar to support NULL, including the IS NULL unary operator and IfNull function. Comparisons, string concatenations, and Boolean operations have special behavior to handle NULL.
The = operator is not useful in determining whether a value is NULL because the value of a NULL operand is unknown. Siebel applications provide the IS NULL operator, which evaluates to TRUE if its operand is NULL and to FALSE if its operand is not NULL.
The IfNull function has two arguments and returns the value of either the first or second argument depending on whether the first argument is NULL. IfNull (a,b) returns a if a is not NULL or returns b if a is NULL.
Use caution when querying flag fields. The comparison operators <> and NOT IN do not allow the evaluation of fields that are null. Since flag fields are defaulted to null, a workflow condition of <>'Y' does not work. There are three ways to work around this problem:
When either side of an arithmetic operation is NULL, the operation returns NULL of the appropriate type, except for string concatenation. In a string concatenation operation, NULL simply adds no characters. For example, 1 + 2 is 3, 1 + NULL is NULL (of type Integer), "Fred" + ", Smith" is "Fred, Smith", but "Fred" + NULL is "Fred."
|Siebel Developer's Reference|