Indicator variables used to assign special values

You can use an indicator variable to set a null value for a column in an INSERT or UPDATE statement.

There are two forms of indicators for INSERT and UPDATE statements: normal indicators and extended indicators. When you use normal indicators, an indicator set to any negative value is interpreted as the null value. When you use extended indicators, the negative values have several different meanings. Both forms of indicators can be used for inserts and updates that are part of a MERGE statement as well.

When processing update and insert using normal indicators, 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 column is set from the associated host variable value.

For example, you can specify that a value be updated in a column, but you know that an actual value is not always known. 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 a non-null value, set PHONEIND to zero; otherwise, to tell SQL that NEWPHONE contains a null value, set PHONEIND to a negative value.

Using extended indicators provides your application with more flexibility when writing INSERT and UPDATE statements. In addition to providing the null value, you can set an indicator to indicate that the default value for a column is used or that the corresponding column is not updated at all.

For extended indicators, the indicator values are interpreted as follows:

An indicator value of 0 means the value for the host variable is assigned to the column.
An indicator value of -1, -2, -3, -4, or -6 means the null value is assigned to the column.
An indicator value of -5 means the default value for the column is assigned.
An indicator value of -7 means that the column is not assigned. This value causes the column to be treated as though it were not listed in the insert or update column list. For an INSERT statement it means the default value is used.

To write an UPDATE statement that can conditionally update several different fields, write it as follows:

EXEC SQL
 UPDATE CORPDATA.EMPLOYEE
   SET PHONENO = :NEWPHONE:PHONEIND,
       LASTNAME = :LASTNAME:LASTNAMEIND,
       WORKDEPT = :WORKDEPT:WORKDEPTIND,
       EDLEVEL = :EDLEVEL:EDLEVELIND
   WHERE EMPNO = :EMPID
END-EXEC.

With this one UPDATE statement, you can update any or all of the columns listed in the SET clause. For example, if you only want to update the EDLEVEL column, set the EDLEVEL variable to the new value and the EDLEVELIND indicator to 0. Set the other three indicators (PHONEIND, LASTNAMEIND, and WORKDEPTIND) to -7. This causes the statement to be processed as though you had written it this way.

EXEC SQL
 UPDATE CORPDATA.EMPLOYEE
   SET EDLEVEL = :EDLEVEL:EDLEVELIND
   WHERE EMPNO = :EMPID
END-EXEC.

You can use extended indicators only if they are explicitly enabled for your program. To specify that your program supports extended indicators, use *EXTIND on the OPTION parameter of the precompiler command or EXTIND(*YES) on the SET OPTION statement.