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
 General SQL Server Forums
 New to SQL Server Programming
 coalesce not working

Author  Topic 

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-06-26 : 02:14:22
I tried coalesce but it's not working. I'm getting zero encountered error on this statement. Both numerator and denominor equal zero. I need the results to be zero.

SUM(CASE WHEN Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 / SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 02:22:14
Where you tried coalesce? your posted code doesnot contain coalesce? also it would be better if you give a brief idea of what you're trying to do here.
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-26 : 02:46:41
maybe that's the point. coalesce doesn't work right when you don't use it.


elsasoft.org
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-26 : 03:11:28
And "getting zero encountered error" is most likely "divide by zero" which is a mathematical impossibility. If SUM(CASE WHEN Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) sums up to 0 then you will get this error.

- Lumbago
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-06-26 : 03:15:13
declare @Hold_GainPermit char(1)
declare @Advoc_Hold char(3)
select @Hold_GainPermit = 'N'
select @Advoc_Hold = 'No'

select
SUM(CASE WHEN @Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 /
CASE WHEN (SUM(CASE WHEN @Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0) = 0
THEN 1.0
ELSE SUM(CASE WHEN @Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0
END

Greetings
Webfred
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 03:26:47
or:-

select
ISNULL(SUM(CASE WHEN @Hold_GainPermit = 'Y' THEN 1 ELSE 0 END) * 1.0 /
NULLIF(SUM(CASE WHEN @Advoc_Hold = 'Yes' THEN 1 ELSE 0 END) * 1.0,0),0)
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-06-26 : 03:34:42
that looks fine!
I have never seen/used NULLIF before...

;o)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 03:37:52
quote:
Originally posted by webfred

that looks fine!
I have never seen/used NULLIF before...

;o)


it just puts NULL whenever it encounters 0 in denominator so that result becomes NULL. then we can use ISNULL or CAOLESCE to change it value we want (i just gave 0 as default value)
Go to Top of Page
   

- Advertisement -