Fixing SQL Divide By Zero Error

We had an issue yesterday with reports failing in Tracker.Net yesterday. The exception was “Divide by Zero Error Encountered.” I first thought it might be an error in a report itself. But searching for the error pointed to the SQL Server query instead. This Stack Overflow article describes the problem pretty well. The problem with my reports was that I was calculating the “percent score” on a test. I was accounting for a null “maximum score” but not for a zero maximum score. I haven’t checked the SCORM specification to see if a zero value is even valid, but it doesn’t really matter as at least one customer has data like that. Here is the original section of a query:

Tracker_SessionLessonStudentLog.LessonTestScore / ISNULL(Tracker_SessionLessonStudentLog.LessonMaxScore, 100) AS SessionPercentScore

The ISNULL() function checks if LessonMaxScore is null and, if so, substitutes 100. But it doesn’t address the situation where LessonMaxScore has an actual value of 0. That’s what caused the divide by zero error. As suggested in the article referenced above, I added the NULLIF() function as shown below.

Tracker_SessionLessonStudentLog.LessonTestScore / NULLIF(ISNULL(Tracker_SessionLessonStudentLog.LessonMaxScore, 100), 0) AS SessionPercentScore

So if the inside expression is 0, the whole divisor becomes null and the SessionPercentScore becomes null.

Advertisements

About Jeff Rhodes
Jeff Rhodes is the Chief Technical Officer and owner of Platte Canyon Multimedia Software Corporation, a leader in developing commercial software that Improves the Lives of Training Developers. He graduated at the top of his class at the Air Force Academy, where he earned a Bachelor of Science in Electrical Engineering. Jeff received a Masters degree in Economics from the London School of Economics, which he attended under a British Marshall Scholarship. Jeff is the author of "Programming for e-Learning Developers: ToolBook, Flash, JavaScript, & Silverlight" and "VBTrain.Net: Creating Computer and Web Based Training with Visual Basic .NET." He also co-wrote "The ToolBook Companion." He has had numerous articles on training development published and is a frequent presenter at conferences both in the U.S. and Europe. Jeff lives in Colorado Springs with his wife Sue and sons Derek and Michael.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: