Avoid Division by Zero in Query/400
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)
|
THANKS TED .. THIS WAS A BURNING ISSUE FOR ME SINCE LONG TIME .. AND WITH THIS SIMPLE SOLUTION IT’S GONE.
THANK AGAIN
Thanks much for this solution!