Divide by zero error encountered.

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 error encountered.

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-13 : 05:15:13

Hi Forumer's

I was encoutering error when im running this script.
I have a zero value in requiredQty which im using to calculate the usage. I tried to use ISNULL and Coalesce but i got an error.

any idea to fixed this issue. Thanks.


Source Data:
Partno               |FinishedQty|RequiredQty|NewQty|TranMonth	
----------------------------------------------------------------------------
SHELL3003110ENLIGHTEN|	943      |943        |39    |2012-05-01 00:00:00.000
SALV3003110ENLIGHTEN|	943      |0          |9     |2012-05-01 00:00:00.000

.....
Select  partno
	, FinishedQty
	, RequiredQty
	, NewQty
	, Usage1=ROUND(ISNULL((NewpartsQty/FinishedQty*100),0),0)
	, Usage2=ROUND(ISNULL((NewpartsQty/RequiredQty*100),0),0)
	--, Usage1=ROUND((NewpartsQty/Finishedfone*100),0)
    --, Usage2=ROUND((NewpartsQty/RequiredQty*100),0)
	, TranMonth
From CTE				
Where NewPartsQty > 0
group by partno, FinishedQty, RequiredQty, NewQty, TranMonth
Order by Partno, TranMonth

THank you in advance..

jov

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 05:34:54

Usage1=case when Finishedfone <> 0 then round(NewpartsQty/Finishedfone*100,0) end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-13 : 05:54:02

Thank for the reply.
Still got an error due to have a zero value in newpartsqty and requiredqty.

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

	
)			
Select  partno
	, Finishedfone
	, RequiredQty
	, NewPartsQty
    , Usage1=Case when Finishedfone <> 0 or NewpartsQty <> 0 then round(NewpartsQty/Finishedfone*100,0) end
 	, Usage2=Case when RequiredQty <> 0 or NewpartsQty <> 0 then round(NewpartsQty/RequiredQty*100,0) end
	, TranMonth
From CTE				
Where NewPartsQty > 0
group by partno
	, Finishedfone
	, RequiredQty
	, NewPartsQty
	, TranMonth
	, Case when Finishedfone <> 0 or NewpartsQty <> 0 then round(NewpartsQty/Finishedfone*100,0)END
	, Case when RequiredQty <> 0 or NewpartsQty <> 0 then round(NewpartsQty/RequiredQty*100,0)END
Order by Partno, TranMonth


Partno               |FinishedFone|RequiredQty|NewpartsQty|TranMonth	
----------------------------------------------------------------------------
SHELL3003110ENLIGHTEN|	943      |943        |39    |2012-05-01 00:00:00.000
SALV3003110ENLIGHTEN|	943      |0          |9     |2012-05-01 00:00:00.000
LGE3285	            |   511	|3066	    |0	  |2012-04-01 00:00:00.000

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 06:45:04

Case when Finishedfone <> 0 or NewpartsQty <> 0
Needs to be "and" rather than "or"

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-06-13 : 21:03:39

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources