MOD (SQL)
Synopsis
MOD(dividend,divisor)
{fn MOD(dividend,divisor)}
Description
MOD returns the mathematical remainder (modulus) from the dividend by the divisor.
MOD can be specified as either a standard scalar function or an ODBC scalar function with curly brace syntax.
-
If dividend and divisor are positive, it returns a positive modulo, or zero.
-
If dividend and divisor are both negative, it returns a negative modulo, or zero.
-
If dividend or divisor is NULL, it returns a NULL.
-
If divisor is 0, it generates a SQLCODE -400 with a %msg <DIVIDE> error.
-
If divisor is larger than dividend, it returns dividend.
The precision reported for MOD (either syntax form) is the same as the precision report for the arithmetic expression dividend/divisor.
ANSI Operator Precedence
The behavior of the MOD function with a single negative operand depends on the Apply ANSI Operator Precedence configuration setting:
-
If Apply ANSI Operator Precedence is not applied, the behavior of MOD with a negative operand is the same as the # modulo operator. Both return the short count (the amount required to reach the next multiple), not the modulo. For example, 12#7 returns a modulo of 5; –12#7 returns a short count of 2. If dividend is negative, the short count is a positive value, or zero. If divisor is negative, the short count is a negative value, or zero.
-
If Apply ANSI Operator Precedence is applied (the default at InterSystems IRIS 2019.1 and subsequent), the behavior of MOD with a negative operand is to always return a modulo. If dividend is negative, it returns a negative modulo, or zero. If divisor is negative, it returns a positive modulo, or zero.
The behavior of the # modulo operator is not affected by the Apply ANSI Operator Precedence configuration setting.
Arguments
dividend
A number that is the numerator (dividend) of the division.
divisor
A number that is the denominator (divisor) of the division.
MOD returns the NUMERIC data type unless the dividend is data type DOUBLE. If dividend is DOUBLE, MOD returns DOUBLE.
Examples
The following example shows the remainder returned by MOD.
SELECT MOD(5,3) AS Remainder
returns 2.
SELECT MOD(5.3,.5) AS Remainder
returns .3.