A NULL condition can be specified for any target column that may contain NULL values. It describes the condition for loading the NULL value as a column value.
Example:
DATALOAD TABLE item
itno 01-08
descr 09-39 NULL IF POS 09-11 = ' '
stock 40-43 INTEGER NULL IF POS 40-43 INTEGER < '0'
min_stock 44-45 INTEGER
price 46-53 DECIMAL(2) NULL IF POS 1 <> 'X'
OR POS 46-53 DECIMAL < '0'
weight 54-57 REAL
INFILE ...
The NULL condition begins with NULL IF. The syntax is similar to the selection criterion for input records described in the preceding section.
The system tests whether the condition is true for each input record; if it is true, the NULL value is inserted, if it is not true, the value of the assigned record field is inserted.
Columns of the target table which are not specified in the DATALOAD statement are set to the default value in all inserted rows.
Note that columns cannot be set to the NULL value when they are defined in the database catalog with the KEY or NOT NULL option. LOAD rejects the definition of a NULL condition for such columns.
Columns with a default value definition other than NULL cannot contain a NULL value either, but in this case LOAD inserts the explicit default value instead of the NULL value; i.e., it evaluates the statement as "default if pos".
The DEFAULT NULL condition can be utilized when the representation of the NULL values is the same for all columns of the file. In this case, the character string, which is otherwise specified after every NULL IF POS, is defined only once as file option NULL '...'.
Any number of DEFAULT NULL conditions is allowed in the LOAD statement. During evaluation, the shorter value is padded with blanks.
Example:
DATALOAD TABLE address
identification 1
telephone 5 - 15 DEFAULT NULL
city 19 - 58 DEFAULT NULL
INFILE customer.data
NULL '- '
The LOAD statements generated by DATAEXTRACT FOR DATALOAD look like the statements shown in this example.