• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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)



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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    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

    Lawson Unveils Cloud-Based PLM for Clothes Makers LUG Issues Call to iASP Arms for ISVs

    2 thoughts on “Avoid Division by Zero in Query/400”

    • Ravin says:
      March 9, 2017 at 11:08 pm

      THANKS TED .. THIS WAS A BURNING ISSUE FOR ME SINCE LONG TIME .. AND WITH THIS SIMPLE SOLUTION IT’S GONE.
      THANK AGAIN

      Reply
    • Ed Galatians says:
      July 26, 2018 at 2:05 am

      Thanks much for this solution!

      Reply

    Leave a Reply Cancel reply

Volume 11, Number 7 -- February 23, 2011
THIS ISSUE SPONSORED BY:

WorksRight Software
Botz & Associates, Inc.
System i Developer

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

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle