Divide by Zero - 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 2005 Forums

Transact-SQL (2005)

Divide by Zero

Author

Topic

Randybrazeau
Starting Member

1 Post

Posted - 2008-02-10 : 22:07:44

Good day,
I have a query..something like this. (Just for an example)

select *
from inv_item
where (price \ cost) < 20

I know I have records where the price = 0 so I get a divide by zero error but I want to return records that the price = zero as well as records that calcualte < 20.
Any ideas?
Thanks

Randy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 22:58:28

You wont get divide by zero if price=0 as 0/cost always yields 0 unless cost =0. so you problem is that you might have cost=0 values. do this to prevent error and include those records too in result:-

select *
from inv_item
where ISNULL((price \ NULLIF(cost,0)),0) < 20

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 04:30:08

or

select *
from inv_item
where cost>0 and (price \ cost) < 20

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 04:39:18

select *
from inv_item
where price < 20 * cost

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources