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.

About Jeff Rhodes
Jeff Rhodes is the Branch Chief, Program Office Support at the U.S. Office of Personnel Management (OPM). Prior to that, he was the Academic Chief Technical Officer at the United States Air Force Academy and previously a Senior IT Specialist in charge of SharePoint and other key systems at the Academy. Jeff was the founder and Chief Technical Officer of Platte Canyon Multimedia Software Corporation, a leader in developing commercial e-learning software. 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 Master’s degree in Economics from the London School of Economics, which he attended under a British Marshall Scholarship. He is the author of Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More, Programming for e-Learning Developers: ToolBook, Flash, JavaScript, and Silverlight, VBTrain.Net: Creating Computer and Web Based Training with Visual Basic .NET and The ToolBook Companion. He lives in Colorado Springs with his wife Sue and is the proud father of his sons Derek and Michael.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: