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

NULL Operator

The NULL operator in an SQL statement represents a value that is not known or is not applicable. Siebel CRM evaluates an expression that includes a NULL operator differently than it evaluates other operators. NULL is not a value. A comparison function does not operate correctly if a NULL operator exists in the comparison. For instance, if NULL = NULL is not TRUE. Note the following:

• SQL and Siebel CRM provide special functions and grammar that support NULL, including the IS NULL operator and the IfNull function. A comparison, string concatenation, and Boolean operation include special behavior that handles a NULL operator.
• You can set the type for a NULL operator similar to how you set the type for a value. An operand or result can be a NULL string, NULL number, NULL Boolean, and so on.
• If one side of a comparison is:
  • NULL. The comparison returns a NULL of type Boolean.
  • Is not NULL. The comparison returns TRUE or FALSE. For example, 1>2 is FALSE, and 1<NULL is NULL.
• If one side of an arithmetic operation is NULL, then the operation returns NULL of the appropriate type, except for a string concatenation. NULL adds no characters during a string concatenation operation. For example:
  • 1 + 2 is 3
  • 1 + NULL is NULL (of type Integer)
  • "Fred" + ", Smith" is "Fred, Smith"
  • "Fred" + NULL is "Fred"

IS NULL Operator

The IS NULL operator is a unary operator. A unary operation is an operation that includes only one operand. If you use the IS NULL operator, and if the operand for this operator evaluates to:

• TRUE. IS NULL evaluates to NULL.
• FALSE. IS NULL evaluates to not NULL.

You cannot use the = (equal) operator to determine if a value is NULL because a NULL operand does not contain a value.

IfNull Function

The IfNull function contains two arguments and returns the value of the first or second argument depending on if the first argument is NULL. IfNull (a,b) returns one of the following values:

• a if a is not NULL
• b if a is NULL

Siebel CRM sets the return type of the IfNull function to the type of the first argument that it contains, even if this first argument is NULL. Siebel CRM converts the second argument to the type of the first argument before it returns the value of the IfNull function.

Flag Fields and NULL

A flag field is a type of field that Siebel CRM can evaluate to a Boolean value, such as True or False, or Y or N. You can use a flag field to turn functionality on or off. A flag field is typically but not always a calculated field.

If you configure Siebel CRM to query a flag field, then you must use caution. The <> (not equal to) and NOT IN comparison operators cannot evaluate a field that contains a null value. Siebel CRM sets the default value of a flag field to null, so a workflow condition of <>'Y' does not work. To avoid this situation, you can do one of the following:

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