Calculating the remainder using SQL

I had a scenario where I needed to determine the remainder value following a division operation for all the rows in a SQL Table. Rather than performing the calculation myself to determine the remainder there is a SQL scalar function that will return it for me, its name is MOD.

IBM's documentation for MOD states that it "divides the first argument by the second argument and returns the remainder". Its syntax is just:

MOD( < dividend value or variable > , < divisor value or variable > )

For example:

01 VALUES MOD(9, 2)

Will return:

00001 ------ 1

As 9 divided by 2 has a remainder of 1.

I wanted to emulate a more realistic scenario, therefore, I created a SQL Table, which I called TESTTABLE, to contain a list of dividend and divisor values:

01 CREATE TABLE MYLIB.TESTTABLE 02 (DIVIDENT DECIMAL(10,3), 03 DIVISOR DECIMAL(10,3)) ;

I am not going to show the contents of this Table now, as it will be shown in my examples below.

Along with the remainder I also want to display the integer value of the result. For this I am going to use another scalar function, FLOOR. In these examples FLOOR will return the integer value of the result. For example:

01 VALUES FLOOR(9 / 2)

Which returns:

00001 ------ 4

With these two scalar functions I can now build my example of how they work with the data I entered into TESTTABLE.

01 SELECT DIVIDENT,DIVISOR, 02 DIVIDENT / DIVISOR "Calculation", 03 FLOOR(DIVIDENT/DIVISOR) AS "Floor", 04 MOD(DIVIDENT,DIVISOR) AS "Rem" 05 FROM TESTTABLE

Line 1: The first two columns from the results are the two columns from TESTTABLE.

Line 2: I want to show the result of the division of the column DIVIDENT by the column DIVISOR.

Line 3: For this column I want the "floor" value from the calculation.

Line 4: I use the MOD built in function to return the remainder.

The results are:

DIVIDENT DIVISOR Calculation Floor Rem -------- -------- ------------------------- ----- ------- 10.000 3.000 3.333333333333333333333 3 1.000 999.000 88.000 11.352272727272727272727 11 31.000 456.780 123.450 3.700121506682867557715 3 86.430

I can see that the "Floor" column contains the integer of the result, and the "Rem" the remainder. Interestingly the remainder of the third row has decimal places, this is due to the divident and divisor having decimal places too.

I am interested to see how the results from the MOD could differ from RPG's %REM Built in Function. For this I created the following RPG program:

01 **free 02 dcl-f TESTTABLE rename(TESTTABLE : INPUT) alias ; 03 dcl-s DividentNoDec packed(10 : 0) ; 04 dcl-s DivisorNoDec like(DividentNoDec) ; 05 dcl-s Result packed(20 : 10) ; 06 dcl-s Remainder like(Result) ; 07 dow (*on) ; 08 read INPUT ; 09 if (%eof) ; 10 leave ; 11 endif ; 12 Result = DIVIDENT / DIVISOR ; 13 dsply ('Result = ' + %char(Result)) ; 14 DividentNoDec = DIVIDENT ; 15 DivisorNoDec = DIVISOR ; 16 Remainder = %rem(DividentNoDec : DivisorNoDec) ; 17 dsply (' RPG %REM = ' + %char(Remainder)) ; 18 exec sql SET :Remainder = MOD(:DIVIDENT, :DIVISOR) ; 19 dsply (' SQL MOD() = ' + %char(Remainder)) ; 20 enddo ; 21 *inlr = *on ;

Line 2: The file declaration has to have the RENAME, as a SQL Table does not have a record format name unless I give it one, which I did not. And I have the ALIAS to ensure that the program would use all long SQL names for the columns in the Table, rather than the short system column names.

Lines 3 – 6: These are the definitions of the variables I will be using in this program.

Lines 7 – 20: I have this Do-loop to read all the rows from the SQL Table using the RPG READ operation code.

Lines 8 – 11: The Table is read, and if the end of file is encountered the processing leaves the Do-loop.

Line 12: I calculate the result of the division operation.

Line 13: I display the result using the DSPLY operation code.

Lines 14 and 15: The %REM BiF requires that if I use variables they "must be numeric values with zero decimal positions", therefore, I have to move the contents of the Table columns DIVIDENT and DIVISOR, both defined as decimal(10,3), to DividentNoDec and DivisorNoDec, defined as packed(10,0).

Line 16: Calculate the remainder using the %REM.

Line 17: Display %REM's remainder.

Line 18: Calculate the remainder using the MOD scalar function. Here I can use the Table's columns.

Line 19: Display MOD's remainder.

The results for the three rows in TESTTABLE are:

DSPLY Result = 3.3333333333 DSPLY RPG %REM = 1.0000000000 DSPLY SQL MOD() = 1.0000000000 DSPLY Result = 11.3522727272 DSPLY RPG %REM = 31.0000000000 DSPLY SQL MOD() = 31.0000000000 DSPLY Result = 3.7001215066 DSPLY RPG %REM = 87.0000000000 DSPLY SQL MOD() = 86.4300000000

The only difference between the %REM and MOD is where decimal places are needed for the remainder, this is when MOD produces the more accurate result.

 

You can learn more about this from the IBM website:

• SQL MOD scalar function
• SQL FLOOR scalar function
• RPG %REM BiF

 

This article was written for IBM i 7.6, and should work for some earlier releases too.