SAND CDBMS Tools Reference Guide
Data Loader (ndlm)

The NULLIF and MISSINGIF Statements

NULLIF and MISSINGIF statements can be included after a field-mapping declaration to set NULL or MISSING values in the field when a certain condition is met. The NULLIF and MISSINGIF statements apply to the field alongside which they appear; they can refer to the values of any field declared previously or subsequently.

Note:
When used together, the PIC, NULLIF, and MISSINGIF statements must be specified in the following order: PIC, then NULLIF, then MISSINGIF.

NULLIF

The NULLIF keyword instructs ndlm to place a NULL value in a field when the specified condition is met. The NULLIF statement has the following syntax:

NULLIF field-name <condition-expression>

The <condition-expression> argument can be any valid NDL++ expression, using functions, variables, field references, or constants. For information about NDL++ variables and functions, consult the appropriate sections below. When the comparison value is a constant, whether numeric or alphabetic, this value must be enclosed by single quotes ( ' ).

MISSINGIF

The MISSINGIF keyword operates similarly to NULLIF, but instructs ndlm to set the field length to "0" when a specified condition is met. It is used only for fields containing character-type data. The syntax for the MISSINGIF statement is identical to that of the NULLIF statement:

MISSINGIF field-name <condition-expression>

Examples

The following example sets field_1 to NULL when the contents of field_2 is the phrase "string constant".

field1 (1:15) NULLIF field2 == 'string constant'

The following example sets the value of field2 as "missing" (that is, it sets the string length to "0" ) when the contents of field1 is "YES".

field2 (16:15) MISSINGIF field1 == 'YES'