HP 3000 Manuals HP 3000 Manuals
Chapter 7 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
:
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
:
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