ALLBASE/SQL Reference Manual (36216-90216)

232 Chapter8
Expressions
Expression
are evaluated. For more information, refer to the chapter, "Data Types."
• If the value of an indicator variable is less than zero, the value of the corresponding
host variable is considered to be NULL.
NOTE To be consistent with the standard SQL and to support portability of code, it
is strongly recommended that you use a −1 to indicate a NULL value.
However, ALLBASE/SQL interprets all negative indicator variable values as
indicating a NULL value in the corresponding host variable.
• The following expressions can evaluate to NULL:
— Host variable with an indicator variable
— Local variable
— Procedure parameter
— Column
— Add Months function
— DateTime function
— Aggregate function
— CAST function
— String function
• A NULL value in an expression causes comparison operators and other predicates to
evaluate to unknown. Refer to Chapter 9 , “Search Conditions,” for more information on
evaluation of comparison operators and predicates containing NULL values.
• The ? can be used as a host variable or dynamic parameter in an expression as shown in
the following examples:
— In the WHERE clause of any SELECT statement:
SELECT *
FROM PurchDB.Orders
WHERE PartNumber = ?
AND OrderDate > ?
ORDER BY OrderDate
— In the WHERE and SET clauses of an UPDATE statement:
UPDATE PurchDB.Parts
SET SalesPrice = ?
WHERE PartNumber = ?
— In the WHERE clause of a DELETE statement:
DELETE FROM PurchDB.OrderItems
WHERE ItemDueDate
BETWEEN ? and ?
— In the VALUES clause of an INSERT or a BULK INSERT statement. In this example
each ? corresponds in sequential order to a column in the PurchDB.OrderItems