Indicator Variables

Use indicator variables to:

Assign null values
Detect null values
Detect data truncation

Unlike COBOL, SQL supports variables that can contain null values. A null value means that no entry has been made and usually implies that the value is either unknown or undefined. A null value enables you to distinguish between a deliberate entry of zero (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry. For example, a null value in a price column does not mean that the item is being given away free, it means that the price is not known or has not been set.

Important: When a host variable is null, its indicator variable has the value -1; when a host variable is not null, the indicator variable has a value other than -1.

Indicator variables serve an additional purpose if truncation occurs when data is retrieved from a database into a host variable. If the host variable is not large enough to hold the data returned from the database, the warning flag sqlwarn1 in the SQLCA data structure is set and the indicator variable is set to the size of the data contained in the database.

Declaring indicator variables

Indicator variables are always defined as:

pic S9(4) comp-5.

Referencing indicator variables

Together, a host variable and its companion indicator variable specify a single SQL value. The following applies to coding a host variable with a companion indicator variable:

Both variables must be preceded by a colon (:).
Place an indicator variable immediately after its corresponding host variable.
Reference the host variable and indicator variable in a FETCH INTO or SELECT ...INTO statement with or without an INDICATOR clause as follows:
```
:hostvar:indicvar
```
or
```
:hostvar INDICATOR :indicvar 
```
You cannot use indicator variables in a search condition. To search for null values, use the is null construct instead.

Examples

This example demonstrates the declaration of an indiator variable that is used in a FETCH ...INTO statement.

 EXEC SQL
     BEGIN DECLARE SECTION
 END-EXEC
 01 host-var       pic x(4).
 01 indicator-var  pic S9(4) comp-5.
 EXEC SQL
    END DECLARE SECTION
 END-EXEC
    . . .

     EXEC SQL
         FETCH myCursor INTO :host-var:indicator-var
     END-EXEC

The following shows an embedded UPDATE statement that uses a saleprice host variable with a companion indicator variable, saleprice-null:

     EXEC SQL
        UPDATE closeoutsale
           SET temp_price = :saleprice:saleprice-null, 
                listprice = :oldprice   
     END-EXEC

In this example, if saleprice-null has a value of -1, when the UPDATE statement executes, the statement is read as:

     EXEC SQL
        UPDATE closeoutsale
           SET temp_price = null, listprice = :oldprice   
     END-EXEC

This example demonstrates the use of the is null construct to do a search:

     if saleprice-null equal -1
         EXEC SQL
             DELETE FROM closeoutsale 
                    WHERE temp_price is null
         END-EXEC
     else
         EXEC SQL
             DELETE FROM closeoutsale 
                    WHERE temp_price = :saleprice
         END-EXEC
     end-if