Null Values

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.

Together, a host variable and its companion indicator variable specify a single SQL value. Both variables must be preceded by a colon (:). 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.

Within an embedded SQL statement an indicator variable should be placed immediately after its corresponding host variable. For example, the following embedded UPDATE statement 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

You cannot use indicator variables in a search condition. To search for null values, use the is null construct. For example, you can use the following:

     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