Edit: This was a poor example of using the divide by zero handling. I was trying to alter something I’d done in the past and this didn’t work well. I will rewrite this soon with a better example.
I have a few posts on computer columns, the basics of computed columns, using CASE in a computed column, and UDFs in computed columns, but there was another use that someone pointed out to me recently: catching divide by zero errors.
Suppose you have a column that is determining a percentage of profit for some sales. I’ll create a table and include some values:
CREATE TABLE MySales ( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost)/ cost ) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99 INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99 INSERT MySales SELECT 1, 'Soda', 0.25, .99
If I look at the values in the table, I get back these items:
/*------------------------ SELECT Product , Cost , Profit FROM MySales ------------------------*/ Product Cost Profit -------------------- --------------------------------------- --------------------------------------- Bike 150.4500 0.202991026919242 Shoes 23.5500 0.952866242038216 Soda 0.2500 2.960000000000000
Things work great, and everything seems to be fine. However, what if we get some free products that we can sell, say something we made out of scraps, or were given to us as a gift with essentially no cost?
INSERT MySales SELECT 1, 'Keychain', 0, .75
It works. However if I select data:
SELECT Product , Cost , Profit FROM MySales
I get this:
Product Cost Profit -------------------- --------------------------------------- --------------------------------------- Bike 150.4500 0.202991026919242 Shoes 23.5500 0.952866242038216 Soda 0.2500 2.960000000000000 Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.
The computation occurs on query, and it doesn’t work well.
However we can fix this with a function in our computed column. There are a couple choices here. I can use ISNULL and a CASE to build a formula, I could use COALESCE, or I could use NULLIF.
NULLIF returns null if two values are equal. If a NULL is acceptable in my table, I could use that. I could end up with:
CREATE TABLE MySales ( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost) / NULLIF(cost ,0) ) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99 INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99 INSERT MySales SELECT 1, 'Soda', 0.25, .99 INSERT MySales SELECT 1, 'Keychain', 0, .75 GO SELECT Product , Profit FROM MySales
and get back:
Product Profit
——————– —————————————
Bike 0.202991026919242
Shoes 0.952866242038216
Soda 2.960000000000000
Keychain NULL
NULL is a good marker here, but your application needs to handle this and let the user know there is an issue.
You could use COALESCE, which returns the first non-null value. I could see any of these as being a valid formula:
, profit AS (price - cost) / COALESCE(cost ,0)
which uses “0” profit margin as a marker, or even something like:
, profit AS (price - cost) / COALESCE(cost , 999)
which uses 999. Lots of times we’ve used a large, nonsense number as a marker that lets someone know there is a strange value here.
If we converted this to a varchar, it’s possible that we could even use words in there, but I wouldn’t recommend that as downstream uses of this column might involve other calculations.
That’s a short look at how a computed column can solve an easy, common issue: divide by zero.
Credit to Atif Shehzad, whose article I found while researching this.
