Chapter 9 Programming with Indicator Variables in Expressions

Prior to this release, host variable indicator variables could be specified with any output host variable and with some input host variables, including those used as parameters to a data/time input function. With this release, you can specify an indicator variable with any input host variable, and indicators can be present in an expression.

Input host variables and their related input indicator variables can be used to provide column data in INSERT, UPDATE, and UPDATE WHERE CURRENT statements. Use them in any WHERE or HAVING clause in which host variables can be used. You cannot use host variables or indicator variables in a any DDL statement. Refer to the "Host Variables" chapter in your ALLBASE/SQL application programming guide for further details regarding host variables.

The indicator variable associated with a host variable determines whether the value in its host variable is considered to be NULL or to be the value stored in the host variable. This is the case for both input and output indicator variables.

Suppose you are writing an application that updates the PurchDB.Inventory table in the DBEnvironment PartsDBE. After selecting a row, you test the QtyOnHand column to see if it contains a negative value. If it does, you want to set the QtyOnHand to NULL. The following example shows the use of an output host variable with an output indicator variable to obtain values by means of a select list and an input host variable with an input indicator variable to supply values in a SET clause:

 

   BEGIN DECLARE SECTION



Declare the QtyOnHand host variable and the QtyOnHandInd indicator variable. In this example, they are used for data output and data input.



   END DECLARE SECTION



   [vellip]



   SELECT QtyOnHand

     INTO :QtyOnHand :QtyOnHandInd

     FROM PurchDB.Inventory

    WHERE PartNumber = :PartNumber



Test the QtyOnHandInd output indicator variable.  If it contains a negative number, the QtyOnHand column is already NULL.  If it contains zero or a positive number, test the QtyOnHand output host variable for a negative number.  If it is negative, update the column value to be null as follows. 



Set the QtyOnHandInd input indicator variable equal to -1.  No matter what value is in the QtyOnHand input host variable, the QtyOnHand column is set to NULL.



   UPDATE PurchDB.Inventory

      SET QtyOnHand = :QtyOnHand :QtyOnHandInd

    WHERE PartNumber = :PartNumber



   [vellip]

You can include an indicator host variable in an expression. In the following example, if QtyOnHandInd is non-negative, the value of QtyOnHand increases by two (The value of QtyOnHandInd is unchanged.):

   UPDATE PurchDB.Inventory

      SET QtyOnHand = {{:QtyOnHand :QtyOnHandInd + 2}}

    WHERE PartNumber = :PartNumber