Eliminate OPNQRYF Divide by Zero Errors

Business Intelligence
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

When using the MAPFLD parameter in the Open Query File (OPNQRYF) command to assign the result of a division to a field, a "division by zero" error may occur. This will happen whenever the field or expression used as a divisor has a value of zero for the record being processed. We cannot test for this condition within OPNQRYF, but we can handle it by rearranging the expression in such a way that, if the divisor is zero, the value yielded by the expression will also be zero.

Suppose we are calculating the number of phone calls per sale for each of the products offered by a telemarketing firm. We could code this as

MAPFLD((CALSAL 'NCALLS/UNSOLD'))

In this code, CALSAL is the calls per sale, NCALLS is the number of calls, and UNSOLD is the units sold. If no units had been sold for a given product, a "division by zero" error message would be issued. To avoid this, rearrange the expression as

 MAPFLD((CALSAL 'NCALLS * UNSOLD/ + %MAX((UNSOLD * UNSOLD) 0.1)')) 

This evaluates to 0, and the divisor evaluates to 0.1, resulting in a quotient with a value of 0. The number to be used along with the divisor as arguments for the %MAX function (e.g., 0.1) should be equal to 10-(2d+1), with d representing the number of decimal places in the divisor. In the example above, UNSOLD had no decimal places; therefore, 10-(2*0+1)=0.1.

- Rafael Valenzuela

BLOG COMMENTS POWERED BY DISQUS