Divide by Zero error

3 Mar 2006 2:31 PM

StvJston

With the following sql I get a divide by zero error. Can somebody help me
with the syntax to fix this?

Thanks in advance
SUM(CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) < (m.SecondaryEERSpec -
(-1 * (@LowerSpec * m.SecondaryEERSpec))) OR
(p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
(@UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END) as SecondaryEERFailures

3 Mar 2006 3:21 PM

Kevin Bowker

How about checking for p.SecondaryWatts first?

SUM(

    CASE WHEN (p.SecondaryWatts =0) THEN -1
        WHEN (p.SecondaryCapacity/p.SecondaryWatts) <
            (m.SecondaryEERSpec - (-1 * (@LowerSpec * m.SecondaryEERSpec)))
        OR
            (p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
(@UpperSpec * m.SecondaryEERSpec))
        THEN 1 Else 0 END

) as SecondaryEERFailures

Show quote

"StvJston" wrote:

> With the following sql I get a divide by zero error. Can somebody help me
> with the syntax to fix this?
>
>
>
> Thanks in advance
> SUM(CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) < (m.SecondaryEERSpec -
> (-1 * (@LowerSpec * m.SecondaryEERSpec))) OR
> (p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
> (@UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END) as SecondaryEERFailures
>
>

3 Mar 2006 3:28 PM

StvJston

Kevin,

Thanks for your reply. I just got it figured out and did just what you
suggested.

SUM(CASE WHEN p.SecondayWatts = 0 THEN 0
ELSE CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) <
(m.SecondaryEERSpec - (-1 * (@LowerSpec * m.SecondaryEERSpec))) OR
(p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
(@UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END END) as
SecondaryEERFailures,

Show quote

"Kevin Bowker" wrote:

> How about checking for p.SecondaryWatts first?
>
> SUM(
>
>     CASE WHEN (p.SecondaryWatts =0) THEN -1
>         WHEN (p.SecondaryCapacity/p.SecondaryWatts) <
>             (m.SecondaryEERSpec - (-1 * (@LowerSpec * m.SecondaryEERSpec)))
>         OR
>             (p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
> (@UpperSpec * m.SecondaryEERSpec))
>         THEN 1 Else 0 END
>
> ) as SecondaryEERFailures
>
> "StvJston" wrote:
>
> > With the following sql I get a divide by zero error. Can somebody help me
> > with the syntax to fix this?
> >
> >
> >
> > Thanks in advance
> > SUM(CASE WHEN (p.SecondaryCapacity/p.SecondaryWatts) < (m.SecondaryEERSpec -
> > (-1 * (@LowerSpec * m.SecondaryEERSpec))) OR
> >                     (p.SecondaryCapacity/p.SecondaryWatts) > (m.SecondaryEERSpec +
> > (@UpperSpec * m.SecondaryEERSpec)) THEN 1 Else 0 END) as SecondaryEERFailures
> >
> >