The evaluation of an expression returns a value or supplies a table (table functions).
Expressions can occur in:
Column selection (SELECT expression, SELECT expression)
predicates in search conditions (e.g. WHERE clause, HAVING clause)
assignments (INSERT, MERGE or UPDATE statement)
SQL statements which are used in routines (e.g. CASE statement)
An expression consists of operands and can include operators. The operators are used on the results of the operands.
The result of the evaluation is an alphanumeric, national, numeric or time value.
A table function returns a table as a result.
The operands are not evaluated in a predefined order. In certain cases, a partial expression is not calculated if it is not required for calculating the total result.
When an operand is evaluated with a function call, the function is first performed and then the function call replaced by the resulting value or the table which is returned.
Syntax diagram of an expression:
expression ::=
{
value |
[ table .] {
column |
{ column
( posno ) | column
[ posno ] } |
{ column
( min..max ) | column
[ min..max ] }
} |
function |
subquery |
monadic_op expression |
expression dyadic_op expression |
case_expression |
cast_expression |
( expression )
}
column ::= unqual_name
posno ::= unsigned_integer
min ::= unsigned_integer
max ::= unsigned_integer
monadic_op ::= { + | - }
dyadic_op ::= { * | / | + | = | || }
value
Alphanumeric value, national value, numeric value or time value (see section "Values" ).
table
Name of the table containing column. If a correlation name has been defined for the table, specify the correlation name instead of the table name.
column
Name of the column from which the values are to be taken.
pos_no
Unsigned integer
The value is taken from the (pos_no-col min+1)th column element of the multiple column column and can be used as an atomic value.
If column is not a multiple column, pos_no is smaller than col min or pos_no is greater than col max, an error message is issued.
col min and col max are the smallest and largest position numbers of the multiple column.
min .. max
Unsigned integers
The value is the aggregate from the column elements (min-col min+1) to (max-col min+1) of the multiple column column.
If column is not a multiple column, min is not smaller than max, min is smaller than col min or max is greater than col max, an error message is issued.
col min and col max are the smallest and largest position numbers of the multiple column.
pos_no or min .. max omitted:
column cannot be a multiple column.
function
Function (see section "Function").
subquery
Subquery (see section "Subquery") that returns exactly one value.
monadic_op
Monadic operator that sets the sign. expression must be numeric and cannot be a multiple value with a dimension > 1.
+ The value remains as it is.
- The value is negated.
dyadic_op
Dyadic operator. Neither of the operand expressions can be a multiple value with a dimension > 1.
a * b
Multiply a with b.
The expressions a and b must be numeric.
If a and b are integers or fixed-point numbers, the result is an integer or fixed-point number with t a+t b significant digits with a maximum number of 31 digits.
The number of digits to the right of the decimal point is r a+ r b, with a maximum number of 31 digits.
t a and t b are the total number of significant digits for a and b.
r a and r b are the number of digits to the right of the decimal point for a and b respectively.
If a or b is a floating-point numbers, the result is a floating-point number with a total number of significant digits of 24 bits for REAL numbers and 56 bits for DOUBLE PRECISION numbers.
If the result value is too big for the resulting data type, an error message is issued. If the total number of significant digits is too big, the number is rounded.
a / b
Divide a by b.
The expressions a and b must be numeric.
If a and b are integers or fixed-point numbers, the result is an integer or fixed-point number with 31 significant digits.
The number of digits to the right of the decimal point is 31-l a-r b, at least however 0.
l a is the number of digits to the left of the decimal point for a.
r b is the number of digits to the right of the decimal point for b.
If a or b is a floating-point numbers, the result is a floating-point number with a total number of significant digits of 24 bits for REAL numbers and 56 bits for DOUBLE PRECISION numbers.
If the result value is too big for the resulting data type or the value of b is 0, an error message is issued. If the total number of significant digits is too big, the number is rounded.
a + b
Add a and b.
The expressions a and b must be numeric.
If a and b are integers or fixed-point numbers, the result is an integer or fixed-point number with l max+r max+1 significant digits with a maximum number of 31 digits.
The number of digits to the right of the decimal point is r max.
l max is the larger of the two numbers of digits to the left of the decimal point for a and b.
r max is the larger of the two numbers of digits to the right of the decimal point for a and b.
If a or b is a floating-point numbers, the result is a floating-point number with a total number of significant digits of 24 bits for REAL numbers and 56 bits for DOUBLE PRECISION numbers.
If the result value is too big for the resulting data type, an error message is issued. If the total number of significant digits is too big, the number is rounded.
a - b
Subtract b from a.
The expressions a and b must be numeric.
If a and b are integers or fixed-point numbers, the result is an integer or fixed-point number with l max+r max+1 significant digits with a maximum number of 31 digits.
The number of digits to the right of the decimal point is r max.
l max is the larger of the two numbers of digits to the left of the decimal point for a and b.
r max is the larger of the two numbers of digits to the right of the decimal point for a and b.
If a or b is a floating-point numbers, the result is a floating-point number with a total number of significant digits of 24 bits for REAL numbers and 56 bits for DOUBLE PRECISION numbers.
If the result value is too big for the resulting data type, an error message is issued. If the total number of significant digits is too big, the number is rounded.
a || b
Concatenate a and b.
The expressions a and b must result in alphanumeric or national values.
If a and b are of the data type CHAR, the result is of the data type CHAR with a length of l a+l b (in characters), and this sum may not be greater than 256.
If a and b are of the data type NCHAR, the result is of the data type NCHAR with a length of l a+ l b (in code units), and this sum may not be greater than 128.
If a or b is of the data type VARCHAR, the result is of the data type VARCHAR with a length of l a+l b (in characters), but at most 32 000.
If a or b is of the data type NVARCHAR, the result is of the data type NVARCHAR with a length of l a+ l b (in code units), but at most 16 000. l a and l b are the lengths of a and b.
If a result of the type CHAR is longer than 256 characters or the result of the type NCHAR is longer than 128 characters, an error message is issued.
If a result of the type VARCHAR is longer than 32 000 characters, the string is truncated from the right to a length of 32 000 characters and if a result of the type NVARCHAR is longer than 16 000 characters, the string is truncated from the right to a length of 16 000 characters. If characters are removed that are not blanks, an error message is issued.
case_expression
CASE expression (see section "CASE expression").
cast_expression
CAST expression (see section "CAST expression").
Precedence
Expressions enclosed in parentheses have highest precedence.
Monadic operators take precedence over dyadic operators.
The operators for multiplication (*) and division (/) take precedence over the operators for addition (+) and subtraction (-).
Operators for multiplication all have the same precedence level.
Operators for addition all have the same precedence level.
Operators with the same precedence level are applied from left to right.
When expression is an unqualified name unqual_name for which there is both a column and a routine parameter or a local variable with this name in the area of validity, the routine parameter or the local variable is used.
Recommendation The names of routine parameters and local variables should differ from column names (e.g. by assigning a prefix such aspar_orvar_).