Using Indicator Variables in Data Manipulation

Data Manipulation with SQL › Data Manipulation Operations › Using Indicator Variables in Data Manipulation

Using Indicator Variables in Data Manipulation

Indicator Variables in SELECT or FETCH

When a column value is retrieved into a host variable that has an associated indicator variable, the DBMS assigns a value to the indicator variable:

Indicator variable value	Meaning
-1	The value assigned to the host variable was null. The actual content of the host variable is unpredictable.
0	The host variable contains a non-null value that has not been truncated.
1 or greater	The host variable contains a truncated value. The value in the indicator variable is the length in bytes of the original untruncated value.

Retrieving a Null Value

Since a null value is not valid in the program language, the program must test for -1 in the indicator variable and direct processing to handle null value retrieval as needed if the test is true.

Null Retrieval Example

In the following example, the program initializes two numeric host variables to zero:

MOVE ZERO TO VAC-TAKEN.
MOVE ZERO TO SICK-TAKEN.

If the next statement now retrieves null values from the VAC_TAKEN and SICK_TAKEN columns, the value of VAC-TAKEN and SICK-TAKEN are still zero because the actual content of the host variables is unchanged when nulls are retrieved:

EXEC SQL
   SELECT VAC_TAKEN,
          SICK_TAKEN
     INTO :VAC-TAKEN INDICATOR :VAC-TAKEN-I,
          :SICK-TAKEN INDICATOR :SICK-TAKEN-I
     FROM BENEFITS
     WHERE EMP_ID = :EMP-ID
END-EXEC.

Indicator Variables in Inserts and Updates

When the program issues a statement to store a value contained in a host variable, the statement optionally can name the associated indicator variable.

If the statement names the indicator and the indicator variable value is 0, the DBMS stores the actual content of the host variable. If the indicator variable value is -1, the DBMS stores a null value instead of the actual content of the host variable.

Update Examples With Indicator Variables

In the next example, the program assigns 0 to the indicator variable after changing the value of the host variable VAC-TAKEN. CA IDMS stores the actual content of VAC-TAKEN on the subsequent update:

ADD INPUT-VAC-TAKEN TO VAC-TAKEN.
MOVE ZERO TO VAC-TAKEN-I.
 .
 .
 .
EXEC SQL
   UPDATE BENEFITS
     SET VAC_TAKEN = :VAC-TAKEN INDICATOR :VAC-TAKEN-I
     WHERE EMP_ID = :EMP-ID
END-EXEC.

By omitting reference to the indicator variable in the UPDATE statement, the program can achieve the same result of storing the actual content of the host variable:

ADD INPUT-VAC-TAKEN TO VAC-TAKEN.
 .
 .
 .
EXEC SQL
   UPDATE BENEFITS
     SET VAC_TAKEN = :VAC-TAKEN
     WHERE EMP_ID = :EMP-ID
END-EXEC.

Similarly, the program can store a null value without naming the indicator variable:

EXEC SQL
   UPDATE BENEFITS
     SET VAC_TAKEN = NULL
     WHERE EMP_ID = :EMP-ID
END-EXEC.