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.

The following table lists the types of arithmetic operations and their functions.

Table 2-13 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

The following table indicates the relationships between the data types of the operands used in arithmetic operations (binary operations) and the data type of the result when the first operand data type is SMALLINT, INTEGER, DECIMAL, SMALLFLT, or FLOAT.

Table 2-14 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

This describes the relationships between the data types of the operands used in arithmetic operations (binary operations) and the data type of the result when the first operand data type is CHAR, VARCHAR, MCHAR, or MVARCHAR, or when the first operand data type is SMALLINT, INTEGER, DECIMAL, SMALLFLT, or FLOAT and the second operand data type is CHAR, VARCHAR, MCHAR, or MVARCHAR.

Character string data is converted to the following numeric data types that represents the value of the character string. The resulting data type of the arithmetic operation is determined using the numeric data type after conversion, according to Table 2-14 Relationships between the data types of operands of arithmetic operations (binary operations) and the data type of a result:

Arithmetic operations are performed using the data format already indicated. The data format of the result for a prefix operation is the same as the data format of the operand. The following table describes the resulting precision and decimal scaling position when the data type of a result of an arithmetic operation is DECIMAL.

Table 2-15 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 = max_prec
s = max(0,max_prec-((p1 - s1) + s2))
DECIMAL(p',s') (p > max_prec)p '= max_prec
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:
max_prec is the maximum precision value of the DECIMAL type. The table below provides more details about max_prec. For details about the pd_sql_dec_op_maxprec operand, see the manual HiRDB Version 9 System Definition.

Table 2-16 Maximum precision value of the DECIMAL type

System common definition pd_sql_dec_op_maxprec operandFirst operand precision p1 and second operand precision p2max_prec value
29 or omittedp1[Figure] 29 and p2[Figure] 2929
p1 > 29 or p2 > 2938
38Any38
Note 3: 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 are executed using numeric data. If character string data is specified, arithmetic operations are executed after the character string data is converted to numeric data.
  2. The following value expressions, in which the result is character string data, cannot be specified in an arithmetic operation:
    • USER
    • SQLERRM_OF_LAST_CONDITION
  3. When specifying an arithmetic operation that includes character string data in the following locations, only a character string literal or mixed character string literal can be specified as character string data:
    • Selection expression
    • Argument of a set function
    • Argument of a user-defined function (includes system-defined scalar functions)
    • GROUP BY clause
    • Value expression of a VALUES clause corresponding to a division column
    • Value expression of a VALUES clause in an INSERT statement or a SET clause in an UPDATE statement referenced with a new value correlation name
    • Value expression of a SET clause in an UPDATE statement referenced with an old correlation name
  4. Embedded variables and value expressions involving the ? parameter only cannot be specified on both sides of an arithmetic operation (+, -, *, /).
  5. When arithmetic operations of different data types are nested, the intermediate results must be handled without any loss in accuracy.
  6. 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. When dividing DECIMAL values, if the total of the number of digits in the integer part of a dividend (p1 - s1) and the number of digits in the decimal fraction part of a divisor (s2) is greater than or equal to max_prec ((p1 - s1) + s2[Figure] max_prec), the decimal scaling position of the division result is 0. To obtain the digits after the decimal point, use the scalar function DECIMAL before the division to decrease the precision (p1) of the dividend or the decimal scaling position (s2) of the divisor.
    Reference
    The following expressions can be obtained from Table 2-15 Precision and the decimal scaling position of a result when the data type of a result of an arithmetic operation is DECIMAL.

    p1=max_prec-s+s1-s2
    s2=max_prec-s+s1-p1

    Example
    If the pd_sql_dec_op_maxprec operand of the system common definition is 29 or this operand is omitted, dividing the sum (SUM) of column C1 (DECIMAL(12,2)) and column C2 (DECIMAL(12,2)) in Table T1 results in the division of DEC(29,2) and DEC(29,2), and the result is 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

  3. If arithmetic operations and concatenation operations are mixed, the data type of the result is determined by the order of operation and result of each operation.
    Example:

    Figure 2-5 Data type of result

    [Figure]