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.
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.
Indicator variables are always defined as:
pic S9(4) comp-5.
:hostvar INDICATOR :indicvar
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