Expansion of Value-expression

Values and Value Expressions › Expansion of Value-expression

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.

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 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.

routine-parameter

Specifies a routine parameter to be used as a single operand in the value expression.

Note: For more information about 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.

Note: For more information about expanded local-variable, see Local Variables.

*, /, +, -, ||

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:

Unary operations from left to right.
Multiplication and division from left to right.
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

Note: 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