8.4.4 MOD
Returns the remainder after dividing the dividend by the divisor.
- Organization of this subsection
(1) Specification format
scalar-function-MOD ::= MOD(dividend,divisor) dividend ::= value-expression divisor ::= value-expression
(2) Explanation of specification format
- dividend:
-
Specifies the dividend.
The following rules apply:
-
Specify the dividend in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
Specify numeric data for the dividend. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.
-
You cannot specify a dynamic parameter by itself for the dividend.
-
- divisor:
-
Specifies the divisor.
The following rules apply:
-
Specify the divisor in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
Specify numeric data for the divisor. For details about numeric data, see (1) Numeric data in 6.2.1 List of data types.
-
You cannot specify 0 for the divisor. If you specify 0, a divide-by-zero error is generated.
-
You cannot specify a dynamic parameter by itself for the divisor.
-
(3) Rules
-
The data type of the execution result is determined by the data types of the dividend and the divisor, as shown in the following table.
Table 8‒4: Data type of the execution result of the scalar function MOD Data type of the dividend
Data type of the divisor
Data type of the execution result
INTEGER
INTEGER
INTEGER
SMALLINT
SMALLINT
DECIMAL
DECIMAL
DOUBLE PRECISION
DOUBLE PRECISION
SMALLINT
INTEGER
INTEGER
SMALLINT
SMALLINT
DECIMAL
DECIMAL
DOUBLE PRECISION
DOUBLE PRECISION
DECIMAL(p,0)
INTEGER
INTEGER
SMALLINT
SMALLINT
DECIMAL
DECIMAL
DOUBLE PRECISION
DOUBLE PRECISION
DECIMAL(p,s)
when s ≥ 1
INTEGER
DECIMAL
SMALLINT
DECIMAL
DOUBLE PRECISION
DOUBLE PRECISION
DOUBLE PRECISION
INTEGER
DOUBLE PRECISION
SMALLINT
DECIMAL
DOUBLE PRECISION
- Note
-
If the data type of the execution result is DECIMAL, the precision and scaling are determined as follows:
Precision (p) = MIN(py-sy+s, 38)
Scaling (s) = MAX(sx, sy)
When calculating the precision and scaling of the execution result when MOD(x,y) is specified, let DECIMAL(px,sx) be the data type of x and DECIMAL(py,sy) be the data type of y.
If the data type of x or y is SMALLINT, use DECIMAL(10,0) for the calculation, and if it is INTEGER, use DECIMAL(20,0).
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If the dividend or the divisor has a null value, the execution result will be a null value.
-
The execution result will have the same sign as the dividend.
-
If you specify DOUBLE PRECISION type data for the scalar function MOD, beware of calculation errors associated with the DOUBLE PRECISION data type. For example, the execution result of the following is not zero:
MOD(5.0E-1,1.0E-1) → 9.9999999999999978E-2
This is due to the fact that 0.1 does not have a finite binary representation (0.1 and 1.0E-1 are not exactly equal). If you require an exact value for the execution result of the remainder, use the DECIMAL type.
(4) Examples
- Example 1:
-
Determine the remainder after dividing the values of column C1 in table T1 by 3.
SELECT MOD("C1",3) FROM "T1"
- Example 2:
-
Determine the remainder after dividing the values of column C1 in table T1 by the values of column C2.
SELECT MOD("C1","C2") FROM "T1"