Background documentationExpression (expression) Locate this document in the navigation structure

 

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

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

  1. The sign (+ | -) has a higher precedence than the additive and multiplicative operators.

  2. The multiplicative operators have a higher precedence than the additive operators.

  3. The multiplicative operators have different priorities.

  4. The additive operators have different priorities.

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

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