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 operation		Meaning	Function
+	(Unary operation)	Positive sign	Leaves the sign unchanged.
	(Binary operation)	Addition	Adds the second operand to the first operand.
-	(Unary operation)	Negative sign	Reverses the sign.
	(Binary operation)	Subtraction	Subtracts the second operand from the first operand.
*	(Binary operation)	Multiplication	Multiplies the first operand by the second operand.
/	(Binary operation)	Division	Divides the first operand by the second operand.

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
	SMALLINT	INTEGER	DECIMAL	SMALLFLT	FLOAT
SMALLINT	INTEGER	INTEGER	DECIMAL	SMALLFLT	FLOAT
INTEGER	INTEGER	INTEGER	DECIMAL	FLOAT	FLOAT
DECIMAL	DECIMAL	DECIMAL	DECIMAL	FLOAT	FLOAT
SMALLFLT	SMALLFLT	FLOAT	FLOAT	SMALLFLT	FLOAT
FLOAT	FLOAT	FLOAT	FLOAT	FLOAT	FLOAT

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 operation	Precision and decimal scaling position
	Addition and subtraction	Multiplication	Division
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(p₁,s₁)

Data type of operand 2: DECIMAL(p₂,s₂)

Note 2: INTEGER is treated as DECIMAL(10,0).

SMALLINT is treated as DECIMAL(5,0).

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.

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 (p₁-s₁) in the integer part of the dividend and the number of digits (s₂) in the fractional part of the divisor is greater than or equal to 29 ((p₁-s₁)+s₂ 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 (p₁) or the scaling factor (s₂) 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