fhg
Volume 11, Number 7 -- February 23, 2011

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)



                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot


Sponsored By
BOTZ & ASSOCIATES, INC.

Free 1-Hour Security Consultation
with Patrick Botz

                                                 Need advice you can trust on security audits,
                                                 PCI, HIPAA or SOX requirements, 3rd party
                                                 software, rational password management,
                                                 public authority or Security Level 40?

                                                 Spend an hour with Patrick Botz - FREE -
                                                 and get actionable info and tips
to get you
                                                 started. Custom, 100% independent advice

                                                 from the guy who helped architect Power
                                                 Systems / IBM i security.

                                                 Click to set up your free session with Pat.
                                                 World-class security expertise is no longer just
                                                 for the big guys!


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

PowerTech:  FREE Webinar! An Auditor's View: Assessing IBM i Security Risks in 15 minutes. March 2
RJS Software Systems:  Integrate RPG, SQL and Excel for maximum ROI with RPG2SQL Integrator
Northeast User Groups Conference:  21th Annual Conference, April 11 - 13, Framingham, MA


 

IT Jungle Store Top Book Picks

BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

The iSeries Express Web Implementer's Guide: List Price, $49.95
The iSeries Pocket Database Guide: List Price, $59
The iSeries Pocket SQL Guide: List Price, $59
The iSeries Pocket WebFacing Primer: List Price, $39
Migrating to WebSphere Express for iSeries: List Price, $49
Getting Started with WebSphere Express for iSeries: List Price, $49
The All-Everything Operating System: List Price, $35
The Best Joomla! Tutorial Ever!: List Price, $19.95


 
The Four Hundred
New Power Systems VP Talks IBM i Strategy, Roadmaps

Humans Fight, But Watson's Chips Beat Quiz Champs

Lotus on IBM i: A Chat with Some Users

As I See It: The Digital Uprising

Zend Gently Nudges Customers to New PHP Runtime

Four Hundred Stuff
Infor Has High Hopes for New S&OP Application

At mindSHIFT, IBM i Hosting Options Abound

nuBridges Unveils TaaS, a Hosted Data Tokenization Service

Qualys Launches Open Source Web App Firewall Project

IBM Delivers Super Fast IPS

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
September 25, 2010: Volume 12, Number 39

September 18, 2010: Volume 12, Number 38

September 11, 2010: Volume 12, Number 37

September 4, 2010: Volume 12, Number 36

August 28, 2010: Volume 12, Number 35

August 21, 2010: Volume 12, Number 34

TPM at The Register
Oracle debuts carrier-grade Sparc T3 servers

How to build your own Watson Jeopardy! supermachine

Intel sends 'Poulson' Itaniums to the shrink

Swiss boffins go nuts for Cray supers

HP reinvents self as data center designer

Novell shareholders approve Attachmate buyout

Cray wraps 2010 on high note

IBM answer machine makes chumps of trivia chimps

Nvidia's Q4 padded with Intel cash

IBM tweaks blade and network gear

Dell to buy AMD?

Humans shamed in round two of Jeopardy! showdown

THIS ISSUE SPONSORED BY:

WorksRight Software
Botz & Associates, Inc.
System i Developer


Printer Friendly Version


TABLE OF CONTENTS
Secure DB2 for i Database Server Access by IP Address

Avoid Division by Zero in Query/400

Image Catalogs: Another Timesaving Method for Upgrade or Installs

Four Hundred Guru

BACK ISSUES




 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2011 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement