Division by 0 in query

In my query, there's a mathematical expression that takes a value from one table and divides it by another value (X).

The problem is that X can be 0 sometimes and then I get an error.

How can I prevent errors like this for the case of X=0?

In access I would use IIF function, but it doesn't appear in SQL SERVER views.

Thanks.

SELECT CASE X WHEN 0 THEN 0 ELSE 10/X -- your division here with the actual column ENDFROM Table

Thanks

-Mark post(s) as "Answer" that helped you

|||

Before you divide, you can check the value of X. Example,

If X = 0 then

response.write "Invalid division."

response.end

End If

|||

e_screw:

SELECT
CASE X
WHEN 0 THEN 0
ELSE 10/X -- your division here with the actual column
END
FROM Table

No Disrespect sir, There is no doubt that this query will work But my question is Dose it works even when filed (X) is null and at the same time checks for zero divisor ?

So my query will be like this :

SELECT *, CASE COALESCE (X, 0) WHEN 0 THEN 0 ELSE COALESCE (10 / X, 0) END AS Expr1
FROM Tbl_something

OR

SELECT *, ISNULL(10/ NULLIF (X, 0), 0) AS Expr1
FROM Tbl_something

Here 10 will be replaced with ur actual column i.e., diviedent and x will be divisor column

Regards,
Shri

|||

shrinidhi:

There is no doubt that this query will work But my question is Dose it works even when filed (X) is null and at the same time checks for zero divisor ?

Well, I was giving an example of how to do a conditional select using SELECT CASE in SQL. The datatype and its null validity should be checked by the user. I am not even checking if the datatype of column X is integer or varchar.

Thanks

|||

in your Selection Query

select like this

Select Isnull(X,1) from

if is only for division and multiplication

Divide by 0 in query

Hi, I want to something very simple...

a query that divides two fields and does not crap out when it gets a divide by 0 error.

this is my query...

select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(cache.respondentcount/nodes.inviteecount * 100) as percentage
from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname

PLEASE HELP!!CREATE FUNCTION returnNullIf0 (@.Num Varchar(100))
RETURNS Varchar(100) AS
--if the passed value is 0, the function returns null
--needs to be used whereever the value is used as denominator
BEGIN
DECLARE @.NewNum as varchar(100)
if isnumeric(@.Num) = 1
Begin
IF round(@.Num, 5)= 0
BEGIN
Select @.NewNum = null
END
Else
Select @.NewNum = @.Num
END
ELSE
BEGIN
Select @.NewNum = @.Num
END
Return(@.NewNum)
END

go


select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(returnNullIf0(cache.respondentcount)/returnNullIf0(nodes.inviteecount) * 100) as percentage
from
cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname|||Originally posted by ngillis
Hi, I want to something very simple...

a query that divides two fields and does not crap out when it gets a divide by 0 error.

this is my query...

select
nodes.shortname,
nodes.inviteecount,
cache.respondentcount,
(cache.respondentcount/nodes.inviteecount * 100) as percentage
from cpd_orgnodes13 nodes, cpd_rollupcache102 cache
where
cache.functioncode = nodes.functioncode
and nodes.depth = 1
order by shortname

PLEASE HELP!!

Also, you could use case:

(cache.respondentcount/case when isnnodes.inviteecount=0
then 1 -- or whatever you want
else isnnodes.inviteecount
end * 100)|||It somewhat depends on what you want to return when the divisor is zero. Zero? Null? A message?

blindman