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 |
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=29-s+s1-s2
s2=29-s+s1-p1
SELECT DEC(SUM(C1),27,2)/SUM(C2) FROM T1