Indicator variables used to set null values

You can use an indicator variable to set a null value in a column.

When processing UPDATE or INSERT statements, SQL checks the indicator variable (if it exists). If it contains a negative value, the column value is set to null. If it contains a value greater than -1, the associated host variable contains a value for the column.

For example, you can specify that a value be put in a column (using an INSERT or UPDATE statement), but you may not be sure that the value was specified with the input data. To provide the capability to set a column to a null value, you can write the following statement:

EXEC SQL
 UPDATE CORPDATA.EMPLOYEE
   SET PHONENO = :NEWPHONE:PHONEIND
   WHERE EMPNO = :EMPID
END-EXEC.

When NEWPHONE contains other than a null value, set PHONEIND to zero by preceding the statement with:

MOVE 0 to PHONEIND.

Otherwise, to tell SQL that NEWPHONE contains a null value, set PHONEIND to a negative value, as follows:

MOVE -1 TO PHONEIND.