SQL MOD scalar function in DB2 for i SQL

SQL MOD scalar function in DB2 for i SQL

MOD

The MOD function returns the remainder by dividing the first numeric expression by the second numeric expression.

Syntax

MOD(numeric-expression1, numeric-expression2)

Formula used to calculate the remainder

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

The parameters can also be character/graphic string data type as it is implicitly cast to numeric value of DECFLOAT(34).

The data type of result is based on the passed parameters data type as follows:

If passed params are large/small integers, resultant data type is large integer.

If both params are intger or atleat 1 parm is big integer, the resultant data type is big integer.

If one param is integer and other is decimal, then resultant data type is decimal with same precision and scale as decimal param.

If both params are decimal, then resultant data type is decimal. However, the result scale will be MIN(Param1_Precision - Param1_Scale, Param2_Precision - Param2_Scale) + MAX(Param1_Scale, Param2_Scale) and the scale is MAX(Param1_Scale, Param2_Scale).

If one param is floating point number and other is not or both floating point number then result is double precision floating type.

Example

SELECT               
MOD(4,2),            
MOD(5,2),            
MOD(5,2.5),          
MOD(5,2.3),          
MOD(7.75,3.2)        
FROM SYSIBM.SYSDUMMY1

Output:

MOD ( 4 , 2 )   MOD ( 5 , 2 )   MOD ( 5 , 2.5 )  MOD ( 5 , 2.3 )  MOD ( 7.75 , 3.2 )
            0               1          .0               .4               1.35