Using NULLIF with cast and decimal? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Using NULLIF with cast and decimal?

Author

Topic

nate4761
Starting Member

2 Posts

Posted - 2013-06-22 : 03:53:47

I am trying to get my code to run in MS SQL Server Management Studio 2012.

USE CMT
GO
DECLARE @rd int
SET @rd='41426'
SELECT [date]
,skillGroup
,interval/48.000 interval
,(sum(acceptable) /cast(sum(callsoffered) as decimal(6,0))) SL
FROM vwForGrpISplit
WHERE [date] IN(@rd)
AND skillGroup IN ('column name 1','column name 2')
GROUP BY [date], skillGroup, interval
ORDER BY skillGroup,[date], interval

However I get the following error message.

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

I am told that using NULLIF will eliminate this error, but can't get it to work correctly. My question is how do I re-write this line
(sum(acceptable) /cast(sum(callsoffered) as decimal(6,0))) SL
to work with NULLIF

I appreciate the help with this.

-Nate

Never judge someone until you've walked a mile in their shoes. That way, you'll be a mile away from them...and you have their shoes ;)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-22 : 10:50:02

You would use NULLIF like shown below. However, that will result in a NULL value for the result. If that is the desired result, then do it like this:

(sum(acceptable) /cast(NULLIF(sum(callsoffered),0) as decimal(6,0))) SL

What it is saying is if the first parameter to the NULLIF function is equal to the second parameter, then return the result as NULL otherwise return the first parameter.

nate4761
Starting Member

2 Posts

Posted - 2013-06-22 : 19:18:17

Thank you, James. This worked like a charm. I think I understand the NULLIF syntax a bit better as well.

Never judge someone until you've walked a mile in their shoes. That way, you'll be a mile away from them...and you have their shoes ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources