• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Two Ways to Prevent Division by Zero in SQL

    May 12, 2010 Ted Holt

    Everybody above the age of 8 knows that division by zero is a no-no, or at least they should. Everybody who’s used SQL for any amount of time knows that we can use a CASE expression to prevent division by zero. But do you know the other way to prevent division by zero in SQL?

    Assume a database file called SomeTable, with fields called Quantity and Amount. Let’s divide Amount by Quantity. First, here’s the usual case statement.

    select itnbr, Amount, Quantity,
           case when Quantity <> 0
                   then Amount / Quantity
                else 0 end
      from SomeTable
    

    If Quantity is not zero, the division takes place. If Quantity is zero, the calculation yields a zero. We could just as easily return any other numeric value, the value one, for example.

    select itnbr, Amount, Quantity,
           case when Quantity <> 0
                   then Amount / Quantity
                else 1 end
      from SomeTable
    

    The other way to prevent division by zero is to use the NULLIF function. NULLIF requires two arguments. If the arguments are equal, NULLIF returns a null value. If they are not equal, NULLIF returns the first value.

    Here’s the SQL.

    select itnbr, Amount, Quantity,
           Amount / nullif(Quantity,0)
      from SomeTable
    

    If Quantity is not zero, the two arguments do not match, and the resulting calculation is Amount divided by Quantity. But if Quantity is zero, the arguments do match, and the calculation is Amount divided by null, which yields a null value.

    If you use this technique, be sure to put the zero in the second argument of NULLIF.



                         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
    ProData

    Simple.  Reliable.  Powerful. 

    IBM i software to help you navigate your universe!

    Use these tools to get where you are going:

    DBU to easily, yet securely, access the data on your IBM i or remote system.
    RDB Connect for powerful record level access to remote data using RPG, CL or COBOL.

    SQL/Pro and Audit offer reliable SQL data processing, tracking and reporting.

    RDR retrieves those records which were deleted in error.

    And more!

    Join our DBUniverse of loyal, happy customers and experience your destination…

    Do IT with DBU!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Help/Systems:  Robot/SCHEDULE Enterprise for UNIX, Linux, Windows & i
    looksoftware:  RPG Open Access Webinar - May 18 at 10am (GMT) & May 19 at 2pm (EDT)
    Essex Technology Group:  May 18-20: IBM POWER7 + COGNOS + VISION, NYC + PA + NJ

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Trubiquity Updates Data Exchange Product for OFTP2 The Power of Software: One on One with Ian Jarman

    Leave a Reply Cancel reply

Volume 10, Number 15 -- May 12, 2010
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL Software
WorksRight Software

Table of Contents

  • Development Environments
  • Two Ways to Prevent Division by Zero in SQL
  • Admin Alert: Diary of a Production System Upgrade, Part 2

Content archive

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

Recent Posts

  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM
  • Critical Security Vulnerability In PowerVM Hypervisor
  • IBM Power: Hosted On-Premises Or In The Cloud?
  • Guru: Watch Out For This Pitfall When Working With Integer Columns
  • As I See It: Bob-the-Bot
  • IBM i PTF Guide, Volume 25, Number 21

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