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

Syntax

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

<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>            ::= + | -

Examples

SQL Tutorial, Arithmetic Operations

Explanation

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

Result of an expression

expression

Value with any data type

factor supplies a NULL value

NULL Value

factor supplies 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

Operand1 (a)

Operand2 (b)

Result

Fixed Point Number
(p precision
s number of decimal places

Fixed point number
(p' precision
s' number of decimal places)

Fixed point number
(p" precision
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.

Condition

Operator

Result

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

+, -

Fixed point number
p''=max(p-s,p'-s')+max(s,s')+1
s''=max(s,s')

(p+p')<=38

*

Fixed point number
p''=p+p'
s''=s+s'

(p-s+s')<=38

/

Fixed point number
p''=38
s''=38-(p-s+s')
Special NULL value, if b=0

Operands are integers, operators are DIV, MOD

Condition

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.