Using NULLIF() To Prevent Divide-By-Zero Errors In SQL
Posted October 3, 2007 at 8:02 AM
Boyan Kostadinov just sent me a cool link to an article that is the final part in a four part series that discusses the SQL NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero errors in a SQL call.
The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code:
Launch code in new window » Download code as text file »
- <!--- Do SQL division with no error protection. --->
- <cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
- SELECT
- ( 45 / 0 ) AS value
- ;
- </cfquery>
... results in a SQL error being thrown:
Error Executing Database Query. [Macromedia] [SQLServer JDBC Driver] [SQLServer] Divide by zero error encountered.
To prevent this sort of error from being thrown, author Hugo Kornelis suggests using a NULLIF() in the divisor of the equation. NULLIF() takes two arguments and returns NULL if the two values are the same and can be used to turn the divisor from a zero into a NULL which, in turn, will force the entire equation to become NULL. Therefore, running this code:
Launch code in new window » Download code as text file »
- <!--- Do SQL division with divide-by-zero protection. --->
- <cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
- SELECT
- ( 45 / NULLIF( 0, 0 ) ) AS value
- ;
- </cfquery>
-
- <!--- Output resulting value. --->
- [ #qDivision.value# ]
... we get the following output:
[ ]
Here, the NULLIF( 0, 0 ) returns NULL since zero is equal to zero, which gets the SQL statement to return NULL, which gets ColdFusion to show an empty string. This is a seemingly pointless example since both zero values are hard coded, but imagine if this were a user-entered value, or even better yet, a SQL aggregate or other calculated value (such as might be used in a report or data mining exercise).
Now, let's say you want to take this one step further and provide a default value for the equation if NULL is encountered (A default value, though not entirely accurate might make your consuming code more compact as it won't have to deal with exception cases). To set a default value, we could use the ISNULL() or COALESCE() functions:
Launch code in new window » Download code as text file »
- <!---
- Do SQL division with divide-by-zero protection. But this,
- time, let's provide a default value if the division is
- not valid.
- --->
- <cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
- SELECT
- (
- ISNULL(
- (45 / NULLIF( 0, 0 )),
- 0
- )
- ) AS value
- ;
- </cfquery>
-
- <!--- Output resulting value. --->
- [ #qDivision.value# ]
Here, we are performing the division as we were above, but then, if that equation returns NULL, our ISNULL() function is going to catch that and return zero as its default value. Therefore, running the above code, we get the following output:
[ 0 ]
As someone who runs a ton of reports on database table (albeit, not in any educated way), this is going to come in very handy. I find that in most cases, having a zero is graphically equivalent to NULL and a whole lot easier to deal with.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
New ColdFusion 9: Centaur Free eCard Available (Saucy)
Older Post
Target ColdFusion Template Gets Compiled Even If Not Executed
Reader Comments
Niiiiiiiiiiiice!
I've had this problem in strange statistics data for a long time and its always been a little bit tricky to over come, this is a nice solution, and its all done in SQL too.
In the past I've pulled the math out of SQL and had CF do it as I had more control over the devision by zero, this makes a hell of a lot more sense.
Nice, Ben!
Thanks,
Rob
@Rob,
Yeah, I feel your pain. I used to use CASE statements in the divisor. It worked, but it was just wordy and distracting. I find this to be much more straight forward and readable.
Is this function specific to SQL Server or will it work on other databases as well? (Oracle, MySQL, etc.)
@Chad,
I think NULLIF() is standard. Not sure about ISNULL(). I think COALESCE() is more standard than ISNULL().
Excellent find! Wish I would have known about this a long time ago - I've always just used a case statement:
case
when isNull(divisor, 0) = 0
then 0
else numerator/divisor end as value
But this seems much nicer!
How about speed between case n nullif
@Ivan,
I would guess that CASE would be faster only because it is inline rather than a method call. But, just a guess.
Could not be easier. Thanks a lot !!
For Oracle, you might try something like this: columnname1/decode(columnname2,0,null)
