• 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
    Fresche Solutions

    Executive Report for IT Leaders

    Why Smart Modernization Is the Path to Successful IBM i Digital Transformation

    Download this report to get answers these key questions:

    • What is driving digital transformation?
    • Where are organizations going wrong with respect to modernization?
    • What is the best / most efficient / smartest way forward?

    Get the Report »

    _________________________________________________________________

    About Fresche Solutions

    Fresche is the leading provider of digital transformation enablement, application modernization and optimization solutions for companies that rely on IBM i Power Systems. Through the power of automated tools and experienced professionals, Fresche delivers smart solutions that bring our clients the best, boldest ideas to fuel transformation and better manage their business and IT. Our low-risk, phased approach is giving companies new ability to digitally innovate and improve operational processes while leveraging IT assets so clients can take advantage of digital solutions including web, mobile, cloud and AI.

    We can help you with:

    • Developing your IT strategy
    • Building an IBM i roadmap
    • Transforming your RPG and CA 2E (Synon) code to modern languages
    • Addressing developing shortages
    • Quickly onboarding new IT staff
    • Increasing developer productivity
    • Integrating with other systems
    • Extending your ERP
    • Understanding your applications
    • Deploying modern interfaces
    • Improving business agility
    • Starting your digital transformation journey

    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

  • When Cloud Meets DevOps on IBM i
  • JD Edwards Roadmap Reveals Decisions To Be Made
  • IBM Completes Migration of Knowledge Center to IBM Documentation
  • Four Hundred Monitor, April 7
  • Crazy Idea Number 615: Variable Priced Power Systems Partitions
  • Do The Math When Looking at IBM i Hosting For Cost Savings
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 1
  • Oracle Versus Rimini Slogs On In Second Decade
  • HCI Is The Dominant Converged System, Probably For Good
  • Skytap To Expand IBM i Cloud Offering

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 © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.