Divide by Zero

When I specify a formula between Computed Column Specification, I have two
zero values, getting Divide by Zero error, any idea how can I avoid this? I
still want SQL Server to display Zero if it is 0/0, is this possible in SQL
Server database?
Thanks
J.CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (50,0)
INSERT INTO #Test VALUES (20,10)
INSERT INTO #Test VALUES (0,0)
SELECT *,
CASE WHEN col1>0 AND col2>0 THEN col1/col2 ELSE 0 END FROM #Test
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||use case, here is an example
create table #test (value1 numeric (12,2),value2 numeric (12,2))
insert into #test
select 1,0 union all
select 1,0 union all
select 5,3 union all
select 4,2
select case value2 when 0 then 0 else value1/value2 end as SomeValue
from #test
http://sqlservercode.blogspot.com/|||Joriv,
Since 0/0 is not equal to zero, you need to make a different
calculation to get what you want. No programming language
should have a setting to display wrong answers (0 for the result of
0/0), so you have to be specific about what you want. One way
to do this in SQL is
CASE WHEN bottomValue = 0 THEN 0 ELSE topValue/bottomValue END
You don't say whether or not you also want a result of 0 if you have
1/0, 2/0 and so on. The example above will give you a result of 0
in these cases as well.
Steve Kass
Drew University
Joriv wrote:

>When I specify a formula between Computed Column Specification, I have two
>zero values, getting Divide by Zero error, any idea how can I avoid this? I
>still want SQL Server to display Zero if it is 0/0, is this possible in SQL
>Server database?
>Thanks
>J.
>
>|||Thanks Folks for all your responses, even I think I can use isnull,
but the idea is i want to use this Computed Column Spec in table designer,
how can I insert a formula which does this? even when i do this
isnull((table.column1/table.column2), 0), still does not work...getting same
error...any clue?
thanks
J.
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||Hi
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 AS CASE WHEN col1>0 AND col2>0 THEN col1/col2 ELSE 0 END
)
INSERT INTO #Test VALUES (50,0)
INSERT INTO #Test VALUES (20,10)
INSERT INTO #Test VALUES (0,0)
SELECT * FROM #Test
"Joriv" <nojunk@.please.com> wrote in message
news:dumteu$7at$1@.reader01.news.esat.net...
> Thanks Folks for all your responses, even I think I can use isnull,
> but the idea is i want to use this Computed Column Spec in table designer,
> how can I insert a formula which does this? even when i do this
> isnull((table.column1/table.column2), 0), still does not work...getting
> same error...any clue?
> thanks
> J.
> "Joriv" <nojunk@.please.com> wrote in message
> news:dumr51$6id$1@.reader01.news.esat.net...
>|||Thanks for the group,
Now I run into another problem, once I set this formula successfully, I
cannot change the value programmatically. anyway I can do this?
Actually what I want to do this, Initally (default) I want to set to a
formula (say col1/col2) but I might change this programmatically in future
if required.
I tried to set this formual in the default/binding value, but I don't think
it likes any formual's over there.
Any ideas?
Thanks
J.
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||Here is the query without case/when.
e.g.
select isnull(a/nullif(b,0),0)[div]
from (select 1 a, 0 b
union all select 2,1)x
-oj
"Joriv" <nojunk@.please.com> wrote in message
news:dumteu$7at$1@.reader01.news.esat.net...
> Thanks Folks for all your responses, even I think I can use isnull,
> but the idea is i want to use this Computed Column Spec in table designer,
> how can I insert a formula which does this? even when i do this
> isnull((table.column1/table.column2), 0), still does not work...getting
> same error...any clue?
> thanks
> J.
> "Joriv" <nojunk@.please.com> wrote in message
> news:dumr51$6id$1@.reader01.news.esat.net...
>|||>> Actually what I want to do this, Initally (default) I want to set to a fo
rmula (say col1/col2) but I might change this programmatically in future if
required. <<
Then use VIEWs instead of a computed column, which is portable, which
can be drop and which allows you to have multiple formulas.
What answer did you want for 0/0? I would go with a NULL or catch the
error.