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?
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.
eval Ratio = %div(Shipments: Orders);
Integer division has its purposes, but for business computing, I have rarely used it.