Avoid Division by Zero in Query/400

Published: February 23, 2011

Hey, Ted:

I want to build a temporary database file to send to a user, but records with a zero divisor do not get written to the output file. Can I prevent Query for i from dividing by zero?

--Bill

Yes, Bill, and it's not difficult. Here's how it's done. But first, some test data.

create table mylib/divbyzero
(Extended dec(5,2), Qty dec(3,0))

insert into mylib/divbyzero values
(500, 2), (0, 12), (6, 0), (0, 0), (100, 3)

Now I have five rows of data to play with, two of which have zero in the Qty field. Here's my first attempt to create an output file where UnitPrice is calculated as Extended divided by Qty.

Field       Expression        Len   Dec
UNITPRICE   extended / qty      5     2

This is what pressing F5 produces:

EXTENDED  QTY   UNITPRICE
 500.00     2     250.00 
    .00    12        .00 
   6.00     0     +++++++
    .00     0     +++++++
 100.00     3      33.33

If I direct the output of this query to a database file, I get error message QRY5053 (Query run failed. Two records added to member ZERO1.) The output file contains the first two rows only.

I can change the query so that UnitPrice is given a zero price when Qty is zero.

Field       Expression               Len   Dec
ISNOT0      qty / (qty - 0.0000001)   1     0

IS0         (isnot0-1) * (isnot0-1)   1     0

UNITPRICE   (extended * isnot0) /
            (qty + (1 * is0))

This is what F5 shows me now.

EXTENDED  QTY   UNITPRICE
 500.00     2      250.00
    .00    12         .00
   6.00     0         .00
    .00     0         .00
 100.00     3       33.33

I created two work fields--IS0 (is zero) and ISNOT0 (is not zero)--that I can use to manipulate the dividend and divisor. IS0 is 1 if the divisor is zero, 0 otherwise. ISNOT0 is just the opposite.

Therefore, for the first record, UnitPrice is calculated as:

(500 * 1) / (2 + (1 * 0))

which is 250.

But for the third record, the calculation is:

(6 * 0) / (0 + (1 * 1))

which is zero.

You don't have to set the UnitPrice to zero. You can plug any number you want. The calculation is only slightly different.

Field       Expression                Len   Dec
UNITPRICE   ((extended * isnot0) +     5     2 
             (999.99 * is0)) /
            (qty + (1 * is0))

For the first record, UnitPrice is calculated as:

((500 * 1) + (999.99 * 0)) / (2 + (1 * 0))

which is 250.

But for the third record, the calculation is:

((6 * 0) + (999.99 * 1)) / (0 + (1 * 1))

which is 999.99.

Plugging a high number makes the bad data stand out.

EXTENDED  QTY   UNITPRICE
 500.00     2     250.00 
    .00    12        .00 
   6.00     0     999.99 
    .00     0     999.99 
 100.00     3      33.33

--Ted

RELATED STORY

Query/400 Handles Zero Dates (Sort Of)