Wednesday, March 21, 2018

Handling divided by zero in SQL

coping with divide by 0 in sql update

One of the messages that annoys me the most is "Attempt to divide by zero". I am sure every programmer, no matter, what language they use, has encountered this at least once. It frustrates me is there is not much we can do to fix the error once it has happened. It annoys me that this can be handled very well within the program to prevent the error.

Before I get started I know there are a couple of different ways the division calculation can be described, I did check. I will be using the following:

   Result = Dividend / Divisor 

It is the Divisor that must be zero for this error to occur, as:

Dividend Divisor Result
0 2 0
2 0 Error!

In RPG the bad calculation looks like:

 Result = Dividend / Divisor ;

If this is placed within a MONITOR group the error is handled and I can give Result any value I want.

monitor ;
  Result = Dividend / Divisor ;
on-error ;
  Result = 0 ;
endmon ; 

In this example if the division fails the Result variable will be changed to zero.

This is, of course, an over simple example. I could have checked if Divisor was equal to zero before performing the division. One of the wonderful things about RPG free format is that I can have multiple calculations on one line, so Divisor could be made up like:

Result = VAR1 / ((VAR2 + VAR3 + VAR4) * VAR5) ; 

Using the monitor group just becomes easier the more complex my calculation gets.

I mentioned SQL in the title of this post so let me get to that. To get the most of using SQL you need to think of groups of data, not at the record level as you do with RPG. If I wanted to recalculate a column on a table I would use an Update statement.

exec sql UPDATE QTEMP.TESTTABLE              
            SET COLUMN3 = COLUMN1 / COLUMN2 ;

dsply ('SQLCOD = ' + %char(SQLCOD)) ; 

This is SQL embedded in a RPG program so I can use the display operation code to show the SQL code variable. If this statement encounters a divide by zero situation I would see this:

DSPLY  SQLCOD = -802 

Looking up that code I see that it is used for "Data conversion or data mapping error". If I look in the job log and prompt the SQL0802 message I can see that it was a divided by zero error.

Message ID . . . . :  SQL0802      Severity . . . . . :  30
Message type . . . :  Diagnostic

Message . . . . :   Data conversion or data mapping error.
Cause . . . . . :   Error type 8 has occurred. Error types and
  their meanings are:

    8 -- Division by zero. 

As the error was found in the second row, only the first row was changed. The statement ends when the error is encountered and the second and later rows are unchanged.

  COLUMN1  COLUMN2  COLUMN3
1     2        2         1 
2     1        0        21 
3     0        1        20 
4     4        2        19  

I could have used this statement, only performing the calculation if COLUMN2 is not zero.

 exec sql UPDATE QTEMP.TESTTABLE
            SET COLUMN3 = COLUMN1 / COLUMN2
          WHERE COLUMN2 <> 0 ;

The problem is that in any row where COLUMN2 is zero, COLUMN3 will still contain the old value.

I have to confess I found this solution in a forum discussing Microsoft SQL. As SQL is pretty universal I was not surprised to find that this will work for Db2 for i too.

The premise of this statement is any number divided by null will be null.

exec sql UPDATE QTEMP.TESTTABLE                        
            SET COLUMN3 = COLUMN1 / NULLIF(COLUMN2,0) ; 

In this statement the NULLIF function changes the value of COLUMN2 to null if it contains zero, and the result of the division is null.

When this statement is run the SQL code is zero, as the error was handled. My table now looks like:

  COLUMN1  COLUMN2  COLUMN3
1     2        2         1
2     1        0          -
3     0        1         0
4     4        2         2

The hyphen ( - ) in COLUMN3 in the second row indicates null.

What would I do if COLUMN3 does not allow null values or I want to use another value to indicate that an error was encountered?

I would use the IFNULL function, to converts null to whatever value I give in the second parameter.

exec sql UPDATE QTEMP.TESTTABLE
       SET COLUMN3 = IFNULL(COLUMN1 / NULLIF(COLUMN2,0),0) ; 

So the NULLIF prevents the divide by zero error by converting COLUMN2 to null. The IFNULL, in turn, converts the null result of the division to -1. My table now looks like:

  COLUMN1  COLUMN2  COLUMN3
1     2        2         1
2     1        0         0
3     0        1         0
4     4        2         2

By using these approaches we can all stop the divide by zero from ever happening again!

 

You can learn more about the NULLIF function from the IBM website here.

 

This article was written for IBM i 7.3, and should work for earlier releases too.

13 comments:

  1. This is great info as usual but imagine coming across an un-commented IFNULL / NULLIF statement in a late night debugging session...

    ReplyDelete
    Replies
    1. It would be a lot better to encounter that than the "Divide by zero" error, which is why you are debugging the program in the first place ;)

      Delete
  2. Why not use commitment control in the first place?!

    ReplyDelete
    Replies
    1. I do not use commitment control in these posts as these examples are just that, examples. And I am running in a test environment on a test box.

      Feel free to use commitment control if you make your own version of these examples.

      Disclaimer policy

      Delete
  3. Just another idea, using a CASE-Clause:

    Update YourTable
    set YourColumn = Case When (a + b + c + d) <> 0
    Then X / (a + b + c + d)
    Else YourColumn End
    ... WHERE conditions;

    ReplyDelete
  4. Sergio L Puentes-ValladaresJuly 2, 2019 at 1:00 PM

    Buenisimo

    ReplyDelete
  5. Great Simon. These are gems
    Thanks
    Andrea DD

    ReplyDelete
  6. Great compilation of RPG Monitor, Sql NULLIF IFNULL... Thank you. am preparing for interview, hope this question comes up :)

    ReplyDelete
  7. Will be hanging onto this one. I've used CASE statements to do something similar, but the NULLIF looks much easier.

    ReplyDelete
  8. Simon, great read and examples. Thanks for sharing.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.