Circumventing Integer Division

March 16, 2011 Hey, Ted

My computer seems to have forgotten how to divide. When I divide one value by another, using SQL, the answer is always exactly one or zero. What gives?

–RPG Professional

This took a little while, but we finally found his problem. First, the background.

Assume a database file with three fields: customer ID, the number of orders from that customer, and the number of shipments to the customer.

select CustID, Orders, Shipments
  from Summary  
          
CUSTID  ORDERS       SHIPMENTS
     1      75              25
     2      10              16
     3      20               0
     4       8               8

Now let’s add a ratio to the query to gauge order fulfillment.

select CustID, Orders, Shipments, 
       Shipments / Orders as Ratio
  from Summary              
CUSTID  ORDERS       SHIPMENTS           RATIO
     1      75              25               0
     2      10              16               1
     3      20               0               0
     4       8               8               1

The problem, as it turned out, was the orders and shipments fields were defined as integer. The programmer had used SQL to create the summary table on the fly, and the system had inferred that the two fields would always contain whole numbers.

Since the two fields were integer fields, SQL used integer division. According to the SQL reference:

If both operands of an arithmetic operator are integers with zero scale, the operation is performed in binary, and the result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer. Any remainder of division is lost.

To get a result with decimal positions, I had him use the DOUBLE function over the two fields. Since both operands were double precision, the system carried out division of real numbers. To get a readable result, I had him use the DEC function.

select CustID, Orders, Shipments,
       dec(double(Shipments) / double(Orders),4,3) as Ratio
  from Summary

CUSTID          ORDERS       SHIPMENTS   RATIO
     1              75              25    .333
     2              10              16   1.600
     3              20               0    .000
     4               8               8   1.000

RPG also supports integer division, if you want it. Use the %DIV built-in function.