Division by zero and computed by column

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

The best way to avoid the problem is to use a CASE statement like:

declare @.aTable table(value1 int, value2 int)
insert into @.aTable values (32, 4)
insert into @.aTable values (5, 0)

select value1,
value2,
case when value2 <> 0 then value1/value2 end
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0 NULL
*/

-- Or maybe:

select value1,
value2,
isnull(convert(varchar(11), case when value2 <> 0 then value1/value2 end), '')
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0
*/

|||

Thank you for your reply.

So you prefer to get the result via select command instead of fixed computed by column, right?

|||I am not sure that I understand that last question; what exactly do you mean?|||

You can use the CASE structure in a computed column definition:

Something like:

Code Snippet

ALTER TABLE MyTable

ADD COLUMN MyComputedCol AS ( CASE WHEN ( [Col1] <> 0 ) THEN ( [Col2] / [Col1] ) END )

|||Thanks for picking me up, Arnie! Again, I am asleep at the wheel! Sheesh! To answer your question, Jan, no, I have no issue against using the computed column. As Anie indicated, the computed column should be just fine. (I'm BRAINDEAD today!)|||Wow, that's amazing. Thank you Kent and Arnie!

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.

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.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/|||IF like this:
Update a
Set X=column b/column c
where Column c<>0

Update a
Set X=0
where Column c=0

any help to you?|||Or perhaps something like:

Update a
Set X= case when columnc=0 then 0 else column b/column c end

MC

"yangyang" <loveflying000@.gmail.com> wrote in message
news:1141834929.055768.75450@.v46g2000cwv.googlegro ups.com...
> IF like this:
> Update a
> Set X=column b/column c
> where Column c<>0
> Update a
> Set X=0
> where Column c=0
> any help to you?