A value of NULL is a special marker that indicates that the value is not known. A value of NULL is different from an empty or zero value. The Data Model therefore handles NULL values differently from empty and zero values. The way that the UNICOM Intelligence Data Model handles NULL generally corresponds to the SQL-92 ANSI standard, although there are one or two important exceptions.

In market research data, a NULL value typically indicates that the question was not asked. When a respondent is asked a categorical or open-ended question but for some reason does not answer, the data generally holds an empty categorical value ({}) or an empty string ("") respectively (although some questions have one or more special categories to indicate that the respondent did not answer). This means that for categorical and text data, you can distinguish between a question that was asked but not answered and one that was not asked at all. However, in numeric data you cannot distinguish questions that were asked but not answered from those that were not asked at all, because the UNICOM Intelligence Data Model currently stores a NULL value for both.

When the auto answer playback feature determines that the input source contains empty case data, it will attempt to enter the empty answer first. If this fails it then generates a random answer.

However, mrScriptBasic handles NULL values differently because scripting languages require a different behavior. For example, if the following code were evaluated using the SQL-92 ANSI standard, the While loop would never be entered when x has a value of NULL:

In a scripting language, users expect the loop to be executed as if x were zero. Therefore mrScriptBasic always treats NULL values as empty or zero values.

When NULL values are included in an arithmetic expression, the UNICOM Intelligence Data Model follows the SQL-92 ANSI standard and evaluates the expression to NULL. For example:

This is because the NULL value indicates that the value is undefined and when you add an undefined value to a known value, the result is, by definition, undefined.

However, mrScriptBasic always treats NULL values in an arithmetic expression as empty or zero values, depending on the data type. For example:

The SQL-92 ANSI standard defines that when a comparison operator is used with a NULL value, the expression should evaluate to False. The UNICOM Intelligence Data Model follows this standard except when two NULL values are compared, when it follows the alternative SQL Server standard and evaluates to True. For example, the following expression evaluates to True:

However, in UNICOM Intelligence Data Model 2.4 and earlier, the Evaluate component automatically converts a NULL value to the data type with which it is being compared, which can lead to erroneous results. For example, the following expressions erroneously evaluate to True in UNICOM Intelligence Data Model 2.4 and earlier:

For example, this means that if you use the following expression in a filter to exclude respondents who are over 60, the filter will erroneously include respondents for whom the age variable holds a NULL value:

However, you can avoid this problem by using IS NOT NULL:

This problem does not occur in UNICOM Intelligence Data Model 2.5 and later.

However, mrScriptBasic always treats NULL values in a comparison expression as empty or zero values, depending on the data type. For example, when the following expressions are used in mrScriptBasic, they evaluate to True:

The way that the functions in the UNICOM Intelligence Function Library handle NULL values varies according to the data type of the parameters involved. When a parameter is of one data type only (such as Categorical), a NULL value is automatically converted to that data type. For example, when a NULL value is used for a Categorical parameter, the NULL value is automatically converted to an empty categorical ({}). This means that these functions handle NULL values differently from the corresponding arithmetic operators when used by the UNICOM Intelligence Data Model (but in a similar way to the corresponding arithmetic operators when they are used in mrScriptBasic):

XUnion.

For example, when you use the + operator to perform a union of a Categorical value and a NULL value, the result is NULL. However, when you use the Union function to perform a union of a Categorical value and a NULL value, the result is the Categorical value because the NULL value is converted to an empty Categorical value.

When you are using one of the functions in the UNICOM Intelligence Function Library on data that may contain NULL values, you should therefore test for NULL values, as shown in the following expression:

When you are using one of the functions in the UNICOM Intelligence Function Library on data that may contain NULL values, you should therefore test for NULL values, for example:

The NULL value handling in the functions in the UNICOM Intelligence Function Library is the same in both the UNICOM Intelligence Data Model and mrScriptBasic.