SAND CDBMS Tools Reference Guide
Data Loader (ndlm)


Previous Topic:
Import Record Specification
Chapter Index
Next Topic:
The SKIPIF Clause


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.

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


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 ( ' ).


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>


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'