2.10 Arithmetic operations

Organization of this section
(1) Types of arithmetic operations
(2) Data types of results of arithmetic operations
(3) Rules
(4) Notes

(1) Types of arithmetic operations

Retrievals involving arithmetic operations can be specified in a value expression.

Table 2-11 shows types of arithmetic operations and their functions.

Table 2-11 Types of arithmetic operations

Arithmetic operationMeaningFunction
+(Unary operation)Positive signLeaves the sign unchanged.
(Binary operation)AdditionAdds the second operand to the first operand.
-(Unary operation)Negative signReverses the sign.
(Binary operation)SubtractionSubtracts the second operand from the first operand.
*(Binary operation)MultiplicationMultiplies the first operand by the second operand.
/(Binary operation)DivisionDivides the first operand by the second operand.

(2) Data types of results of arithmetic operations

Table 2-12 shows the relationships between the data types of the operands of arithmetic operations (binary operations) and the data type of the result.

Table 2-12 Relationships between the data types of operands of arithmetic operations (binary operations) and the data type of a result

Data type of
operand 1
Data type of operand 2
SMALLINTINTEGERDECIMALSMALLFLTFLOAT
SMALLINTINTEGERINTEGERDECIMALSMALLFLTFLOAT
INTEGERINTEGERINTEGERDECIMALFLOATFLOAT
DECIMALDECIMALDECIMALDECIMALFLOATFLOAT
SMALLFLTSMALLFLTFLOATFLOATSMALLFLTFLOAT
FLOATFLOATFLOATFLOATFLOATFLOAT

Arithmetic operations are performed using the data types shown above. In the case of a unary operation, the data type of the result is the same as the data type of the operand. Table 2-13 shows the precision and decimal scaling position of the result when the data type of the result of an arithmetic operation is DECIMAL.

Table 2-13 Precision and the decimal scaling position of a result when the data type of a result of an arithmetic operation is DECIMAL

Data type of result of arithmetic operationPrecision and decimal scaling position
Addition and subtractionMultiplicationDivision
DECIMAL(p,s)p=1+max(p1-s1, p2-s2)+s
s=max(s1,s2)
p=p1+p2
s=s1+s2
p=29
s=max(0,29-((p1-s1)+s2))
DECIMAL(p',s') (p > 29)p'=29
s'=max(s1,s2)
Not applicable
Note 1: Data type of operand 1: DECIMAL(p1,s1)
Data type of operand 2: DECIMAL(p2,s2)
Note 2: INTEGER is treated as DECIMAL(10,0).
SMALLINT is treated as DECIMAL(5,0).

(3) Rules

For the rules for using arithmetic operations, see the rules below and the rules in Section 2.9 Value expressions, value specifications, and item specifications.

  1. Arithmetic operations can be specified on numeric data.
  2. Embedded variables and value expressions involving the ? parameter only cannot be specified on both sides of an arithmetic operation (+, -, *, /).
  3. When arithmetic operations of different data types are nested, the intermediate results must be handled without any loss in accuracy.
  4. The window function cannot be specified in the operation term.

(4) Notes

  1. The rules below apply to arithmetic operations; however, they do not apply (no error results) when the overflow error suppression feature is set:
    • An error occurs when 0 is specified as the value of the second operand in division
    • An error results when overflow occurs during computation
    For details about the operational results that are produced when the overflow error suppression feature is set, see 2.18 Operational results with overflow error suppression specified.
  2. If, in a DECIMAL division, the sum of the number of digits (p1-s1) in the integer part of the dividend and the number of digits (s2) in the fractional part of the divisor is greater than or equal to 29 ((p1-s1)+s2[Figure]29), the scaling factor for the result of the division will be 0. If digits following the decimal point are desired, the DECIMAL scalar function must be used before division is performed to reduce either the precision (p1) or the scaling factor (s2) of the dividend.
    Reference
    The following expressions can be obtained from Table 2-13:

    p1=29-s+s1-s2
    s2=29-s+s1-p1

    Example
    The divisions of the sums of column C1 (DECIMAL(12,2)) and column C2 (DECIMAL(12,2)) in Table T1 result in divisions by DEC(29,2) and DEC(29,2), yielding the result DEC(29,0). If two places following the decimal point are desired, the DECIMAL scalar function can be used to reduce the precision of the dividend as follows:

    SELECT DEC(SUM(C1),27,2)/SUM(C2) FROM T1