SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Using NULLIF with cast and decimal?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nate4761
Starting Member

USA
2 Posts

Posted - 06/22/2013 :  03:53:47  Show Profile  Reply with Quote
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 ;)

Edited by - nate4761 on 06/22/2013 03:57:53

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 06/22/2013 :  10:50:02  Show Profile  Reply with Quote
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.
Go to Top of Page

nate4761
Starting Member

USA
2 Posts

Posted - 06/22/2013 :  19:18:17  Show Profile  Reply with Quote
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 ;)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000