Divide by '0' , IsNull is ignored PLEASE HELP

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 2000 Forums

SQL Server Development (2000)

Divide by '0' , IsNull is ignored PLEASE HELP

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-13 : 11:23:51

Hello friends,
I am trying to create a view and the command completed successfully but when I view the data, it generates error, like this:
Divide by zero error encountered. .... Null value is eliminated by an agrregate or other SET operation...."

this is part of my view:
Select sum_hr/sum_hr2 as sum,
(DATEDIFF(DD, period_to, date_start)) / (DATEDIFF(DD, date_end, date_start)) as total_date
from employee;

When I look at the data, there is some zeros at the sum_ fields and NULL at the DATE_ fields.

Please help.
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-13 : 11:28:46

If you have zeros in the sum_hr2 column or as a result of the second datediff expression then you will need to code around them with some kind of error checking code.

Something like


SELECT 
case when sum_hr2 = 0 then 0 ELSE sum_hr/sum_hr2 END AS sum,
CASE WHEN DATEDIFF(DD, date_end, date_start) = 0 then 0 ELSE (DATEDIFF(DD, period_to, date_start)) / (DATEDIFF(DD, date_end, date_start)) END as total_date
from employee

-------
Moo. :)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-13 : 11:44:26

Woh, you are an expert.
It worked.
Thanks.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-14 : 04:39:54

Good. Make sure that the users of the report realise why the additional zeros are there, mind.

-------
Moo. :)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-15 : 14:04:14

I am trying to use CASE where I have 3 levels of division like this:
a/(b/c)
I tried to use ELSEIF but it does not work.
CASE WHEN B=0 THEN 0
ELSE WHEN C=0 THEN 0
ELSE WHEN B/C=0 THEN 0
ELSE A/(B/C)

IT is not working.

Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-15 : 14:26:21

if either B or C is zero, you cannot divide and will get an error. Both must be non-zero for A/B/C to be valid.

CASE WHEN (B=0) or (C=0) THEN 0 ELSE A/B/C END

or, if you prefer:

CASE WHEN B*C = 0 THEN 0 ELSE A/B/C END

or

CASE WHEN (B<>0) AND (C<>0) THEN A/B/C ELSE 0 END

Don't use the parenthesis the way you have it in your example -- A/(B/C) -- if A,B and C are integers. You might have rounding errors that cause B/C to end up being zero even though neither B or C are zero.

for example:

declare @a int, @b int, @c int

select @a=10,@b=1,@c=10

-- this is OK
select @a/@b/@c

-- this is an error:
select @a/(@b/@c)

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-15 : 15:06:10

Woh, it worked now. I used CASE WHEN (B<>0) AND (C<>0) THEN A/B/C ELSE 0 END.
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources