Divide by zero encountered - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Divide by zero encountered

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-08 : 10:17:58

Hi expert,

I have an SQL Statement that perform a calculation.
Upon running i have encountered an error that say divide by zero encountered. below is the codes that show this error.

btw, upon checking the result of these sum case statement are both zero. thank you in advance.

SUM(CASE WHEN t1.WRKCTRID IN ('xxx','vvv','ttt','ddd','ppp','lllll') THEN ISNULL(NULLIF(CAST(t1.FailedQty_B AS DECIMAL(10,2)),0),0) ELSE 0 END)/

   SUM(CASE WHEN t1.WRKCTRID = 'xxx' THEN ISNULL(NULLIF(CAST(t1.Total_Touch_B AS DECIMAL(10,2)),0),0) ELSE 0 END) AS FTQ_Yield_B


----
ISNULL(SUM(CASE WHEN t1.WRKCTRID IN ('xxx','vvv','ttt','ddd','ppp','lllll') THEN NULLIF(CAST(t1.FailedQty_B AS DECIMAL(10,2)),0) ELSE 0 END)/
   SUM(CASE WHEN t1.WRKCTRID = 'xxx' THEN ISNULL(NULLIF(CAST(t1.Total_Touch_B AS DECIMAL(10,2)),0),0) ELSE 0 END),0) AS FTQ_Yield_B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-08 : 12:56:05

What would you like to do when you encounter zero in the denominator?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-08 : 20:08:50

I would like have a zero value or Nulls.. but preferably 0.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-08 : 22:06:05

Hi Tkizer,

I tried this codes and its working.
I place the value of ISNULL to 1. May i ask you opinion on this. thanks.

SUM(CASE WHEN t1.WRKCTRID IN ('xxx','vvv','ttt','ddd','ppp','lllll') THEN CAST(t1.FailedQty_B AS DECIMAL(10,2)) ELSE 0 END)/

   SUM(CASE WHEN t1.WRKCTRID = 'xxx' THEN ISNULL(NULLIF(CAST(t1.Total_Touch_B AS DECIMAL(10,2)),0),1) ELSE 0 END) AS FTQ_Yield_B

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 02:47:46

Try this

SUM(CASE WHEN t1.WRKCTRID IN ('xxx','vvv','ttt','ddd','ppp','lllll') THEN ISNULL(NULLIF(CAST(t1.FailedQty_B AS DECIMAL(10,2)),0),0) ELSE 0 END)/

NULLIF(SUM(CASE WHEN t1.WRKCTRID = 'xxx' THEN ISNULL(NULLIF(CAST(t1.Total_Touch_B AS DECIMAL(10,2)),0),0) ELSE 0 END),0) AS FTQ_Yield_B

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources