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 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. |
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 | ||||
---|---|---|---|---|---|
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 |
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 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 = 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 |
Table 2-16 Maximum precision value of the DECIMAL type
System common definition pd_sql_dec_op_maxprec operand | First operand precision p1 and second operand precision p2 | max_prec value |
---|---|---|
29 or omitted | p1![]() ![]() | 29 |
p1 > 29 or p2 > 29 | 38 | |
38 | Any | 38 |
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.
p1=max_prec-s+s1-s2
s2=max_prec-s+s1-p1
SELECT DEC(SUM(C1),27,2)/SUM(C2) FROM T1
Figure 2-5 Data type of result