Expansion of Value-expression

The expanded parameters of value-expression represent a single data value or a set of one or more data values in an SQL statement.
idmscu19
The expanded parameters of value-expression represent a single data value or a set of one or more data values in an SQL statement.
This article describes the following information:
2
2
Syntax
Expansion of value-expression
►►─┬───────┬─┬─ aggregate-function ───────────────┬───────────────────────────►    ├─ + ◄──┤ ├─ scalar-function ──────────────────┤    └─ - ───┘ ├─┬───────────────┬─── column-name ──┤              │ ├─ table-name. ─┤                  │              │ └─ alias. ──────┘                  │              ├─ literal ──────────────────────────┤              ├─ host-variable ────────────────────┤              ├─ special-register ─────────────────┤              ├─ ( value-expression ) ─────────────┤              ├─ labeled-duration ─────────────────┤              ├─ dynamic-parameter-marker ─────────┤              ├─ rowid-pseudo-column ──────────────┤              ├─ routine-parameter ────────────────┤              └─ local-variable ───────────────────┘  ►─┬──────────────────────────────────────────────────────────────┬───────────►◄    │ ┌──────────────────────────────────────────────────────────┐ │    └─▼─┬─ * ──┬─┬───────┬─┬─ aggregate-function ───────────────┬┴─┘        ├─ / ──┤ ├─ + ◄──┤ ├─ scalar-function ──────────────────┤        ├─ + ──┤ └─ - ───┘ ├─┬───────────────┬─── column-name ──┤        ├─ - ──┤           │ ├─ table-name. ─┤                  │        └─ ││ ─┘           │ └─ alias. ──────┘                  │                           ├─ literal ──────────────────────────┤                           ├─ host-variable ────────────────────┤                           ├─ special-register ─────────────────┤                           ├─ ( value-expression ) ─────────────┤                           ├─ labeled-duration ─────────────────┤                           ├─ dynamic-parameter-marker──────────┤                           ├─ rowid-pseudo-column ──────────────┤                           ├─ routine-parameter ────────────────┤                           └─ local-variable ───────────────────┘
Parameters
  • +, -
    Specifies the unary arithmetic operation to be performed on the operand that follows:
    • +
      leaves the sign of the operand unchanged. A positive value remains positive. A negative value remains negative.
    • -
      reverses the sign of the operand. A positive value becomes negative. A negative value becomes positive.
    The default is +.
    You can specify unary arithmetic operators with numeric operands only.
  • aggregate-function
    Specifies an aggregate function to be used as an operand in the value expression. For expanded
    aggregate-function
    syntax, see Expansion of Aggregate-function.
  • scalar-function
    Specifies a scalar function to be used as an operand in the value expression. For expanded
    scalar-function
    syntax, see Expansion of Scalar-function.
  • column-name
    Specifies a column to be used as an operand in the value expression. The expression is evaluated once for each value in the named column.
    • table-name.
      Specifies the table, view, procedure or table procedure that includes the named column. For expanded
      table-name
      syntax, see Expansion of Table-name.
    • alias
      .
      Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. The alias must be defined in the FROM parameter of the subquery, query specification, or SELECT statement that includes the value expression.
  • literal
    Specifies a literal to be used as a single operand in the value expression. For expanded
    literal
    syntax, see Expansion of Literal.
  • host-variable
    Specifies a host variable to be used as a single operand in the value expression. For expanded
    host-variable
    syntax, see Expansion of Host-variable.
  • special-register
    Specifies a special register to be used as a single operand in the value expression. For expanded
    special-register
    syntax, see Expansion of Special-register.
  • (value-expression)
    Specifies another value expression to be used as a single operand in the value expression. To be manipulated as a single operand, the value expression must be enclosed in parentheses.
  • labeled-duration
    Specifies a labeled duration to be used as an operand in the value expression. For expanded
    labeled-duration
    syntax, see Expansion of Labeled-duration.
  • dynamic-parameter-marker
    Specifies a dynamic parameter to be used as a single operand in the value expression. For expanded
    dynamic-parameter-marker
    syntax, see Expansion of Dynamic-parameter-marker.
  • rowid-pseudo-column
    Requests the ROWID value to be determined when the SQL statement in which it appears is executed. For expanded 
    rowid-pseudo-column
     syntax, see Expansion of rowid-pseudo-column.
  • routine-parameter
    Specifies a routine parameter to be used as a single operand in the value expression. For expanded
    routine-parameter
    syntax, see Expansion of Routine-parameter.
  • local-variable
    Specifies a local variable to be used as a single operand in the value expression. For expanded
    local-variable
    syntax, see Expansion of Local-variable
  • *, /, +, -, ||
    Specifies the binary arithmetic operation or concatenation operation to be performed on the operands preceding and following the operator.
    Binary arithmetic operators are:
    • *
      multiplies the first operand by the second operand
    • /
      divides the first operand by the second operand
    • +
      adds the second operand to the first operand
    • -
      subtracts the second operand from the first operand
    You can specify binary arithmetic operators with numeric operands only.
    The concatenation operator is:
    • || concatenates the second operand to the first operand
    You can specify the concatenation operator with binary operands, character operands, or graphics operands.
Usage
Order of Evaluation
After evaluating the individual operands, CA IDMS performs the operations in a value expression in the following order:
  1. Unary operations from left to right.
  2. Multiplication and division from left to right.
  3. Addition and subtraction from left to right.
You can use parentheses to override the default order of evaluation. Operations in parentheses are performed first.
For example, the result of the following value expression is 19:
10 * 2 - 1
When the subtraction operation is enclosed in parentheses, the result of the expression is 10:
10 * (2 - 1)
Unary Operators With Signed Numeric Literals
If the operand following a unary operator is a numeric literal that includes a plus or minus sign, the literal must be enclosed in parentheses.
Null Values in a Value Expression
If the value of any of the operands in a value expression is null, the result of the expression is a null value.
Data Type of the Result
The data type of the result of a value expression with one operand is the data type of the operand.
The data type of the result of a numeric value expression with multiple operands is the common data type corresponding to the data type of highest precedence in the expression, as determined by the rules for data type conversion in arithmetic operations.
This table shows the data type of the result of a concatenation operation for each allowable combination of operands:
Operand
Operand
Result
CHARACTER
CHARACTER
CHARACTER
CHARACTER
VARCHAR
VARCHAR
VARCHAR
VARCHAR
VARCHAR
BINARY
BINARY
CHARACTER
BINARY
CHARACTER
CHARACTER
BINARY
VARCHAR
CHARACTER
GRAPHIC
GRAPHIC
GRAPHIC
GRAPHIC
VARGRAPHIC
GRAPHIC
VARGRAPHIC
VARGRAPHIC
VARGRAPHIC
 
For more information about data type conversion, see Comparison, Assignment, Arithmetic, and Concatenation Operations.
Examples
A Single Operand
In the SELECT statement below, the value expressions that identify the data to be selected each consist of a single operand. The first is a column, and the second two are aggregate functions.
select proj_leader_id, count(proj_id), avg(est_man_hours)    from project    group by proj_leader_id;
Multiple Operands
In the UPDATE statement below, the value expression that specifies the new value for SALARY_AMOUNT includes multiple operands. CA IDMS computes the new value by multiplying the value in the SALARY_AMOUNT column by .06, adding the result to the original value in SALARY_AMOUNT, and then adding the value in :MERIT_AMT to the result of the first addition.
EXEC SQL UPDATE POSITION    SET SALARY_AMOUNT = SALARY_AMOUNT + (SALARY_AMOUNT * .06)                        + :MERIT_AMT    WHERE EMP_ID = :EMPLOYEE-ID END-EXEC