• 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
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    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

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

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