8.4.4 MOD

Returns the remainder after dividing the dividend by the divisor.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Examples

(1) Specification format

scalar-function-MOD ::= MOD(dividend,divisor)
 
  dividend ::= value-expression
  divisor ::= value-expression

To Page Top

(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.

To Page Top

(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.

To Page Top

(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"

[Figure]

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"

[Figure]

To Page Top