MOD

The MOD function divides the first argument by the second argument and returns the remainder.

MOD(expression-1,expression-2)

The formula used to calculate the remainder is:

  MOD(x,y) = x - (x/y) * y

where x/y is the truncated integer result of the division. The result is negative only if first argument is negative.

expression-1
An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. A string argument is cast to double-precision floating point before evaluating the function. For more information about converting strings to double-precision floating point, see DOUBLE_PRECISION or DOUBLE.
expression-2
An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. A string argument is cast to double-precision floating point before evaluating the function. For more information about converting strings to double-precision floating point, see DOUBLE_PRECISION or DOUBLE. expression-2 cannot be zero unless either argument is decimal floating-point.

If an argument can be null, the result can be null; if an argument is null, the result is the null value.

The attributes of the result are determined as follows:

  • If both arguments are large or small integers with zero scale, the data type of the result is large integer.
  • If both arguments are integers with zero scale and at least one of the arguments is a big integer, the data type of the result is big integer.
  • If one argument is an integer with zero scale and the other is decimal, the result is decimal with the same precision and scale as the decimal argument.
  • If both arguments are decimal or integer with scale numbers, the result is decimal. The precision of the result is min (p-s,p'-s') + max (s,s'), and the scale of the result is max (s,s'), where the symbols p and s denote the precision and scale of the first operand, and the symbols p' and s' denote the precision and scale of the second operand.
  • If either argument is floating point and the other operand is not decimal floating-point, the data type of the result is double-precision floating point.

    The operation is performed in floating point. If necessary, the operands are first converted to double precision floating-point numbers. For example, an operation that involves a floating-point number and either an integer or a decimal number is performed with a temporary copy of the integer or decimal number that has been converted to double precision floating-point. The result of a floating-point operation must be within the range of floating-point numbers.

  • If either argument is decimal floating-point, the data type of the result is DECFLOAT(34). If the argument is a special decimal floating-point value, the general rules for arithmetic operations apply. See General arithmetic operation rules for DECFLOAT for more information.

If either argument is decimal floating-point and the second operand evaluates to 0, the result is NaN and the invalid operation warning (SQLSTATE 0168D) is issued.1 MOD(1, -Infinity) returns the value 1.

Examples

  • Assume the host variable M1 is an integer host variable with a value of 5, and host variable M2 is an integer host variable with a value of 2.
      SELECT MOD(:M1,:M2)
        FROM SYSIBM.SYSDUMMY1
    Returns the value 1.
  • Assume the host variable M1 is an integer host variable with a value of 5, and host variable M2 is a DECIMAL(3,2) host variable with a value of 2.20.
      SELECT MOD(:M1,:M2)
        FROM SYSIBM.SYSDUMMY1
    Returns the value 0.60.
  • Assume the host variable M1 is a DECIMAL(4,2) host variable with a value of 5.50, and host variable M2 is a DECIMAL(4,1) host variable with a value of 2.0.
      SELECT MOD(:M1,:M2)
        FROM SYSIBM.SYSDUMMY1
    Returns the value 1.50.
1 If *YES is specified for the SQL_DECFLOAT_WARNINGS query option, NaN is returned with a warning. Otherwise, a division by zero warning or error is returned.