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. Extended indicator values can be used for OPEN and EXECUTE statements.

Indicators for insert and update operations

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 a host variable to update 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.

An UPDATE statement that can conditionally update several different fields can be written 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 for INSERT, UPDATE, and MERGE statements 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.

Indicators for execute and open operations

Extended indicators can be used with EXECUTE and OPEN statements as well. Only the -7 (Unassigned) extended indicator value is recognized. For these statements, the extended indicator value determines whether the corresponding host variable is used for the statement or whether the host variable should be omitted from processing.

For example, an open of a cursor could provide four host variables. 

EXEC SQL
 OPEN EMPLOYEE_CURSOR
   USING SUBSET :EMPID, :HIREDATE:HIREDATEIND, :WORKDEPT:WORKDEPTIND, :PHONE:PHONEIND
END-EXEC.
If the statement that was built dynamically for an instance of this cursor only contains parameter markers for the employee ID and work department, the HIREDATEIND and PHONEIND indicator variables should be set to -7. This would be run as if it were written as the following OPEN statement. 
EXEC SQL
 OPEN EMPLOYEE_CURSOR
   USING :EMPID, :WORKDEPT:WORKDEPTIND
END-EXEC.