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