Expressions with Arithmetic Operators

Expressions › Expressions with Arithmetic Operators

Expressions with Arithmetic Operators

If arithmetic operators are used, the result of an expression is derived by the application of the operators to the value of the operands.

Arithmetic operators must not be applied to character strings. Thus, USER + 2 is invalid.

The two types of arithmetic operators are prefix operators and infix operators.

Unary Operators

The prefix operator + (unary plus) does not change its operand.

The prefix operator - (unary minus) reverses the sign of its operand. For example, if the data type of A is small integer, then the data type of -A is large integer.

The first character of the token following a prefix operator must not be a plus or minus sign. For example, --10 is not allowed since 10 is preceded by two minus signs.

Note: Unary operators are not allowed with FLOAT, REAL and DOUBLE PRECISION data types.

Infix Operators

The infix operators and the operation they specify are:

Operator	Operation
+	Addition
-	Subtraction
*	Multiplication
/	Division

The operand of an infix operator must not be a function that includes the keyword DISTINCT.

The value of the second operand of division must not be zero.

Conversions During Arithmetic Operations

Conversion implies that each of the two values are represented in a form compatible for the arithmetic operation.

The following table lists the conversions that must take place before any arithmetic operation can occur.

To determine the data type used in the arithmetic operation, locate the data type of the first operand in the left column. Next, locate the data type of the second operand in the vertical columns to the right.

The data type specified at the intersection of the two columns is the data type used in the actual arithmetic operation. In some cases, this data type is different from that of both operands, indicating that both operands are converted before the arithmetic operation is performed.

Two Integer Operands

If both operands of an arithmetic operator are integers, the operation is performed in binary and the result is INTEGER (large integer). Any remainder of division is lost.

The result of a binary arithmetic operation must be within the range of large integers.

Integer and Decimal Operands

If one operand is an integer and the other is DECIMAL (packed decimal) or NUMERIC (zoned decimal), the operation is performed in DECIMAL (packed decimal).

The operation uses a temporary copy of the integer which has been converted to a DECIMAL number with scale 0. The precision of the temporary copy depends on the characteristics of the operand as shown in the following:

Operand	Precision of Decimal Copy
Column or variable: large integer	11
Column or variable: small integer	5
Literal or column more than 5 digits (including leading zeros)	same
Literal: 5 digits or fewer	5

Two Decimal Operands

If both operands are DECIMAL (packed decimal) or NUMERIC (zoned decimal), the operation is performed in DECIMAL (packed decimal). The result of any decimal arithmetic operation is a decimal number with a precision and scale that are dependent on the operation and the precision and scale of the operands.

If the operation is addition or subtraction, and the operands do not have the same scale, the operation is performed with a temporary copy of one of the operands. The temporary copy is extended with trailing zeros so that its fractional part has the same number of digits as the other operand.

The result of a decimal operation must not have a precision greater than 31.

The result of decimal addition, subtraction and multiplication is derived from a temporary result, which may have a precision greater than 31.

If the precision of the temporary result is not greater than 31, the final result is the same as the temporary result.
If the precision of the temporary result is greater than 31, the final result is derived from the temporary result by the elimination of leading digits so the final result has a precision of 31. The eliminated digits must all be zeros.

Following are formulas defining the precision and scale of the result of decimal operations in SQL.

The following table lists the symbols designating the precision and scale for each operand.

Operand	Precision	Scale
first operand	p	s
second operand	p'	s'

The following table lists the formulas for the precision and scale for the four arithmetic operations.

Operation:
Precision:
Scale:

Addition and Subtraction
min(31, max(p - s, p' - s') + max(s, s') + 1)
max(s, s')

Operation:
Precision:
Scale:

Multiplication
min(31, p + p')
min(31, s + s')

Operation:
Precision:
Scale:

Division
31
If s' <= 15 then
scale=(m - p') - p - s + s'
else
scale=max(s' - p' + 15,0) + 15 - (p - s)
endif

scale=max(scale,3)

m=29 if p is even, or m=30 if p is odd

Floating-point Operands

If either operand of an arithmetic operator is floating-point, the operation is performed in floating-point. Thus, if any element of an expression is a floating-point number, the result of the expression is a floating-point number.

An operation involving a floating-point number and a integer is performed with a temporary copy of the integer. The temporary copy of the integer is converted to floating-point.

An operation involving a floating-point number and a DECIMAL (packed decimal) or NUMERIC (zoned decimal) number is performed with a temporary copy of the decimal number. The temporary copy of the decimal number is converted to floating-point.

The result of a floating-point operation must be within the range of floating-point numbers.