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

Transact-SQL (2005)

Divide by zero error encountered.

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-09-26 : 11:13:23

Am getting a Divide by zero error I know there is away around this but can't remember it here is my code

update prd_sales_wk
set Margin = sales_cost / (gprofit_val * 100)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 11:18:41

quote:
Originally posted by rookie_sql

Am getting a Divide by zero error I know there is away around this but can't remember it here is my code

update prd_sales_wk
set Margin = sales_cost / (NULLIF(gprofit_val,0) * 100)

modify like above

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-26 : 11:20:02

quote:
Originally posted by rookie_sql

Am getting a Divide by zero error I know there is away around this but can't remember it here is my code

update prd_sales_wk
set Margin = ISNULL([sales_cost] / NULLIF([gprofit_val],0),0) * 100

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:20:12

And to avoid INTEGER DIVISION, use

UPDATE	prd_sales_wk
SET	Margin = sales_cost / NULLIF(gprofit_val * 100.0, 0.0)

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-26 : 11:30:04

In the other solutions posted, Margin will be set to null if gprofit_val is equal to zero.

If you prefer not to update is at all under that condition, then this will work:


update prd_sales_wk
set
	Margin = sales_cost / (nullif(gprofit_val,0) * 100)
where
	gprofit_val <> 0

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources