Siebel Developer's Reference > Operators, Expressions, and Conditions >

NULL

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 Business 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.

NULL is typed like a value. An operand or result can be NULL string, NULL number, NULL Boolean, and so on.

IS NULL Unary Operator

The = operator is not useful in determining whether a value is NULL because the value of a NULL operand is unknown. Siebel Business Applications provide the IS NULL operator, which evaluates to TRUE if its operand is NULL and to FALSE if its operand is not NULL.

IfNull Function

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.

The return type of IfNull is the type of its first argument, even if the first argument is NULL. The second argument is converted to the type of the first argument before its value is returned.

Comparisons with NULL

When either side of a comparison is NULL, the comparison returns NULL of type Boolean. Otherwise, the comparison returns TRUE or FALSE. For example, 1>2 is FALSE, and 1<NULL is NULL.

Flag Fields and 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:

• Use IS NOT NULL as comparison operator.
• Use IN ('N',NULL).
• Predefault the business component field to 'N'.

Arithmetic Operations with NULL

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."