Expression (expression)

An expression specifies a value that is generated, if required, by applying arithmetic operators to values.

A list of expressions (expression_list) is often used in an SQL statement.

A distinction is made between the following arithmetic operators:

Additive operators
+ addition

- subtraction
Multiplicative operators
* multiplication

/ division

DIV integer division

MOD remainder after integer division

Structure

Syntax Syntax

<expression> ::= <term>
| <expression> + <term>
| <expression> - <term>
| (<expression>)
| -(<expression>)

<expression_list> ::=
  (<expression>,...)
<term> ::=
  <factor>
| <term> * <factor>
| <term> / <factor>
| <term> DIV <factor>
| <term> MOD <factor>

<factor> ::=
  [<sign>] <value_spec>
| [<sign>] <column_spec>
| [<sign>] <function_spec>
| [<sign>] <set_function_spec>
| <scalar_subquery>
| <expression>

<sign>::=
  +
| -

End of the code.

Examples

SQL Tutorial, Arithmetic Operations

Explanation

The arithmetic operators can only be applied to numeric data types.

	Expression Result
expression	Value with any data type
factor provides a NULL value	NULL Value
factor provides a special NULL value	Special NULL Value
expression leads to a division by 0	Special NULL Value
expression leads to an overflow of the internal temporary result	Special NULL Value

If no parentheses are used, the operators have the following precedence:

The sign (+ | -) has a higher precedence than the additive and multiplicative operators.
The multiplicative operators have a higher precedence than the additive operators.
The multiplicative operators have different priorities.
The additive operators have different priorities.
Operators with the same precedence are evaluated from left to right.

Operands are Fixed Point Numbers

Operand a	Operand b	Result
Fixed point number (p Accuracy s Number of decimal places)	Fixed point number (p' Accuracy s' Number of decimal places)	Fixed point number (p'' Accuracy s'' Number of decimal places) or floating point number

Operand a

Operand b

Result

Fixed point number

(p Accuracy

s Number of decimal places)

Fixed point number

(p' Accuracy

s' Number of decimal places)

Fixed point number

(p'' Accuracy

s'' Number of decimal places)

or floating point number

The data type of the result depends on the operation as well as on the precision and number of decimal places of the operands.

Note that the data type of a column determines its name, and not the precision and number of decimal places in the current value.

Operands are Fixed Point Numbers, Operands are +, - , * or /

The result of addition, subtraction, and multiplication is generated from a temporary result which can have more than 38 valid digits. If the temporary result has no more than 38 valid digits, the final result is equal to the temporary result. Otherwise, a result is generated as a floating point number with 38 places. Decimal places are truncated if necessary.

Conditions for Accuracy and Decimal Places for Operands a and b	Operator	Result
max(p-s,p'-s')	+, -	A fixed point number p''=max(p-s,p'-s')+max(s,s')+1 s''=max(s,s')
(p+p')<=38	*	A fixed point number p''=p+p' s''=s+s'
(p-s+s')<=38	/	A fixed point number p''=38 s''=38-(p-s+s') Special NULL value, if b=0

Conditions for Accuracy and Decimal Places for Operands a and b

Operator

Result

max(p-s,p'-s')

+, -

A fixed point number

p''=max(p-s,p'-s')+max(s,s')+1

s''=max(s,s')

(p+p')<=38

A fixed point number

p''=p+p'

s''=s+s'

(p-s+s')<=38

A fixed point number

p''=38

s''=38-(p-s+s')

Special NULL value, if b=0

Operands are integers, operators are DIV, MOD

Conditions for Operands a and b	Operator	Result
ABS(a)<1E38 and ABS(b)<1E38 and b<>0	DIV	TRUNC(a/b)
b=0	DIV	Special NULL value
ABS(a)>=1E38 and b<>0 or ABS(b)>=1E38	DIV	Error message
ABS(a)<1E38 and ABS(b)<1E38 and b<>0	MOD	a-b*(a DIV b)
b=0	MOD	a
ABS(a)>=1E38 and b<>0 or ABS(b)>=1E38	MOD	Error message

An operand is a floating point number

If one operand is a floating point number, the result of the arithmetic operation is a floating point number.