Handling divided by zero in SQL

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.