2.9 Value expressions, value specifications, and item specifications

Organization of this section
(1) Function
(2) Format
(3) Common rules

(1) Function

Values can be specified in SQL in the formats shown below.

(2) Format

value-expression::={[+|-]primary|value-expression {+|-|*|/}
                [{+|-}] primary|value-expression * primary
                 |value-expression / primary|value-expression |
                 | primary}
primary::={(value-expression)|item-specification
          |unsigned-value-specification
          |set-function|window-function|scalar-function
          |CASE-clause|CAST-specification|labeled-interval
          |function-call|scalar-subquery}
value-expression::={literal|? parameter |:embedded-variable
                [:indicator-variable]
                |USER|CURRENT DATE|CURRENT_DATE
                |CURRENT_TIME|CURRENT_TIMESTAMP[(p)]
                |CURRENT DATE|CURRENT DATE
                |CURRENT TIME|CURRENT TIMESTAMP[(p)]
                |[statement-label.]SQL-variable-name
                |[[authorization-identifier.] routine-identifier.]
                  SQL-parameter-name
                |SQLCODE|SQLCOUNT}
unsigned-value-specification::={unsigned-numeric-literal|general-literal
                         |? parameter|:embedded-variable
                            [:indicator-variable]
                         |USER|CURRENT DATE|CURRENT_DATE
                         |CURRENT_TIME
                         |CURRENT_TIMESTAMP[(p)]
                         |CURRENT DATE|CURRENT DATE
                         |CURRENT TIME
                         |CURRENT TIMESTAMP[(p)]
                         |[statement-label.]SQL-variable-name
                         |[[authorization-identifier.]
                             routine-identifier.]
                               SQL-parameter-name
                         |SQLCODE|SQLCOUNT}
literal::={numeric-literal|general-literal}
general-literal::={character-string-literal|hexadecimal-character-string-literal
                |floating-point-literal
                |national-character-string-literal
                |mixed-character-string-literal}
item-specification::={column-specification
                  |[statement-label.]SQL-variable-name
                  |[[authorization-identifier.] routine-identifier.]
                       SQL-parameter-name
                  |component-specification}

(3) Common rules

  1. Among the operations that can be specified in a value expression, the following are referred to generically as scalar operations: arithmetic operations, date operations, time operations, concatenation operations, the CASE expression, the CAST specification, the window function, and scalar functions.
  2. A value expression is specified in terms of a comparison predicate, comparison value, BETWEEN predicate, IN predicate, LIKE predicate, XLIKE predicate, quantified predicate, Boolean predicate, component specification, function call, column specification, update value, primary, set function, or scalar operation.
  3. Scalar operations are evaluated in the following order:
    (1) Inside the parentheses
    (2) * or /
    (3) +, -, or | |
    However, multiple scalar operations of the same order of evaluation occurring in a value expression are evaluated from left to right.
  4. The maximum number of allocatable nesting levels for scalar operations is 255. The number of nesting levels for scalar operations is the number of nesting levels of parentheses when the parentheses indicating the order of evaluation of the operators +, -, *, /, or || are specified explicitly. The number of nesting levels associated with a scalar function, depending on the type of scalar function involved, is as follows:
    • When the scalar function SUBSTR, VARCHAR_FORMAT, TIMESTAMP_FORMAT, DATE (with a datetime format specified), TIME (with a datetime format specified), or TIMESTAMP (function 3) is specified, the number is 2.
    • For the scalar function VALUE, the number is the number-of-value-expressions-of-arguments + 1.
    • For the other scalar functions, the number is 1.
    For simple CASE expressions and search CASE expressions, the number is the number of WHEN statements; for the CASE abbreviation COALESCE, it is the number-of-value-expressions-of-arguments + 1; for the CASE abbreviation NULLIF, it is 2.
    If a scalar operation for which the operand is a column in a named derived table is specified, the column is derived from the scalar operation, and the named derived table does not create an inner derived table; such a specification is equivalent to specifying a scalar operation deriving a column of a named derived table for which the scalar operation is an operand. In this case, the maximum allowable number of nesting levels for scalar operations may be exceeded.
    If scalar functions and function calls are specified in a value expression, the maximum allowable sum of scalar operation nesting levels and function call nesting levels is 255.
  5. If specified data has the null value, the result of an arithmetic, date, time, or concatenation operation also has the null value.
  6. In an arithmetic, date, or time operation involving division, an error results if 0 is specified as the value of the second operand.
  7. An error results if overflow occurs during an operation.
    For value expressions, see 2.10 Arithmetic operations through 2.13 Concatenation operation.
  8. An SQL parameter name is used in a procedure definition to reference the SQL parameter for that procedure or function.
  9. An SQL variable name is used in a compound statement in a procedure definition or a function definition to reference the SQL variable declared in that compound statement.
  10. If a column, an SQL variable, or an SQL parameter with the same name exists, these names must be qualified with a table specification, a statement label or with [authorization-identifier.]routine-identifier. If these items are not qualified or if they are qualified with the same set of qualifiers, they will be identified on a priority basis in ascending order of the scopes of the names. Thus, the following priority will be effective: columns, SQL variables, then SQL parameters. If an item is valid as a column name, it is identified as a column. If an item, though invalid as a column, is valid as an SQL variable, it is identified as an SQL variable. If an item, though invalid as a column or an SQL predicate, is valid as an SQL parameter, it is identified as an SQL parameter. If an item is invalid as a column, an SQL variable, or an SQL parameter, a syntax error results.
    However, in a handler declaration, statement labels and [authorization-identifier.]routine-identifier outside the handler declaration are not inherited. Consequently, if an SQL variable or an SQL parameter is qualified with a statement label or [authorization-identifier.]routine-identifier within the handler declaration, only the statement label declared in the applicable handler declaration takes effect. The following shows examples of scopes of columns, SQL variables, and SQL parameters. The following examples assume that a table T1 containing columns Y and Z of the INTEGER type is defined:

    [Figure]

    Explanation
    1. The value 10 is assigned to the SQL variable X defined in scope C.
    2. The value 10 is assigned to the SQL variable Z defined in scope B.
    3. The value 10 is assigned to the SQL variable X defined in scope C.
    4. An error occurs (a valid Z is not found in the handler in question).
    5. An error occurs (PPP is not inherited because a handler is being declared).
    6. The value 10 is assigned to the SQL parameter Y.
    7. An error occurs (AAA is not inherited because a handler is being declared).
    8. An error occurs (PPP is not inherited because a handler is being declared).
    9. The value of column Y of table T1 is assigned to the SQL variable X defined in scope B. For Y and Z that are specified in the WHERE clause of the single-row SELECT statement, columns Y and Z are used from table T1.
    10. The value 10 is assigned to the SQL variable X defined in scope E.
    11. The value 10 is assigned to the SQL variable Y defined in scope E.
    12. The value 10 is assigned to the SQL variable X defined in scope E.
    13. The value 10 is assigned to the SQL variable Y defined in scope E.
    14. The value 10 is assigned to the SQL parameter Y.
  11. SQLCODE and SQLCOUNT can be used only in routine control SQL other than an SQL procedure. SQLCODE and SQLCOUNT are used to test to see whether the return code is 100, indicating the result of the execution of the preceding SQL procedure statement, exclusive of the routine control SQL, as well as to reference the number of rows that have been updated. SQLCODE and SQLCOUNT have the INTEGER data type.
  12. A repetition column without a subscript cannot be specified in a scalar operation that is directly specified in a CASE expression search condition and that does not contain a repetition column.
  13. The subscript ANY cannot be specified in a scalar operation that is directly specified in a CASE expression search condition and that does not contain a repetition column.
  14. For details about scalar subqueries, see 2.4 Subqueries.