Divide by zero error encountered

Divide by zero error encountered

Post by tkha.. » Fri, 31 Oct 2003 19:18:11



My ddl:
SELECT cnt,CONVERT(varchar(10),OpnDt, 101)AS Dt,
 SUM(CASE WHEN stype = 'Completed Work' THEN cnt ELSE 0
END) AS Comp,
        SUM(CASE WHEN stype = 'Process Capability' THEN
cnt ELSE 0 END) AS Cap,
        SUM(CASE WHEN stype = 'Staged Work' THEN cnt ELSE
0 END)AS Stag
FROM Lean
WHERE stype NOT IN ('Defects to RRT')
and role = 'setup'
and CONVERT(varchar(10), OpnDt, 101) between '09/15/2003'
and '09/15/2003'
GROUP BY  cnt, CONVERT(varchar(10), OpnDt, 101)

output:
cnt      dt             comp    cap     stag
19      09/15/2003      19      0       0
25      09/15/2003      0       0       25
45      09/15/2003      0       45      0

but when I do calculation:
SELECT dt,  Round((stag / cap) * 100, 0)  as Util
FROM test_vw

getting error:
Divide by zero error encountered.
what should I changed in my first query?

Please let me know.

thanks

 
 
 

Divide by zero error encountered

Post by Adam Machani » Fri, 31 Oct 2003 19:22:24


Round((stag / cap) * 100, 0)

cap is obviously 0 in some cases.  Should it be?  How should those cases be
handled?  You need to deal with them in some way.


Quote:> My ddl:
> SELECT cnt,CONVERT(varchar(10),OpnDt, 101)AS Dt,
>  SUM(CASE WHEN stype = 'Completed Work' THEN cnt ELSE 0
> END) AS Comp,
>         SUM(CASE WHEN stype = 'Process Capability' THEN
> cnt ELSE 0 END) AS Cap,
>         SUM(CASE WHEN stype = 'Staged Work' THEN cnt ELSE
> 0 END)AS Stag
> FROM Lean
> WHERE stype NOT IN ('Defects to RRT')
> and role = 'setup'
> and CONVERT(varchar(10), OpnDt, 101) between '09/15/2003'
> and '09/15/2003'
> GROUP BY  cnt, CONVERT(varchar(10), OpnDt, 101)

> output:
> cnt      dt             comp    cap     stag
> 19 09/15/2003 19 0 0
> 25 09/15/2003 0 0 25
> 45 09/15/2003 0 45 0

> but when I do calculation:
> SELECT dt,  Round((stag / cap) * 100, 0)  as Util
> FROM test_vw

> getting error:
> Divide by zero error encountered.
> what should I changed in my first query?

> Please let me know.

> thanks


 
 
 

Divide by zero error encountered

Post by Alejandro Mes » Fri, 31 Oct 2003 19:21:45


Try,

SELECT dt,  Round((stag / NULLIF(cap, 0)) * 100, 0)  as Util
FROM test_vw

AMB

 
 
 

Divide by zero error encountered

Post by Bria » Fri, 31 Oct 2003 19:36:02


depends what you're trying to do, but you could just do
this:

SELECT dt,  Round((stag / cap) * 100, 0)  as Util
FROM test_vw WHERE cap <> 0

the problem is in your original case statement, you're
setting cap = 0 for an invalid stype.  You could also set
cap to some huge number if it's 0 so your Util would
basically be 0, or you could do another case in your
final select, or...

Quote:>-----Original Message-----
>My ddl:
>SELECT cnt,CONVERT(varchar(10),OpnDt, 101)AS Dt,
> SUM(CASE WHEN stype = 'Completed Work' THEN cnt ELSE 0
>END) AS Comp,
>        SUM(CASE WHEN stype = 'Process Capability' THEN
>cnt ELSE 0 END) AS Cap,
>        SUM(CASE WHEN stype = 'Staged Work' THEN cnt
ELSE
>0 END)AS Stag
>FROM Lean
>WHERE stype NOT IN ('Defects to RRT')
>and role = 'setup'
>and CONVERT(varchar(10), OpnDt, 101)

between '09/15/2003'
Quote:>and '09/15/2003'
>GROUP BY  cnt, CONVERT(varchar(10), OpnDt, 101)

>output:
>cnt      dt             comp    cap     stag
>19  09/15/2003      19      0       0
>25  09/15/2003      0       0       25
>45  09/15/2003      0       45      0

>but when I do calculation:
>SELECT dt,  Round((stag / cap) * 100, 0)  as Util
>FROM test_vw

>getting error:
>Divide by zero error encountered.
>what should I changed in my first query?

>Please let me know.

>thanks

>.

 
 
 

Divide by zero error encountered

Post by tk.. » Fri, 31 Oct 2003 19:47:23


this is what i am getting in 1st query:
cnt        dt          comp     cap     stag
19      09/15/2003      19      0       0
25      09/15/2003      0       0       25
45      09/15/2003      0       45      0

when I run 2nd query:
SELECT dt,  Round((stag / NULLIF(cap, 0)) * 100, 0)  as
Util
FROM t1_vw

this is what I am getting:
dt              util
09/15/2003      NULL
09/15/2003      NULL
09/15/2003      0

this is what I am expecting:
dt              util
09/15/2003      0
09/15/2003      0
09/15/2003      56

Quote:>-----Original Message-----
>Try,

>SELECT dt,  Round((stag / NULLIF(cap, 0)) * 100, 0)  as
Util
>FROM test_vw

>AMB

 
 
 

Divide by zero error encountered

Post by tkha.. » Fri, 31 Oct 2003 19:55:57


You right, so how can I set cap to cnt only?
what should I change in my syntax?
 SUM(CASE WHEN stype = 'Process Capability' then cnt else
0 END) AS Cap

thanks

Quote:>-----Original Message-----
>depends what you're trying to do, but you could just do
>this:

>SELECT dt,  Round((stag / cap) * 100, 0)  as Util
>FROM test_vw WHERE cap <> 0

>the problem is in your original case statement, you're
>setting cap = 0 for an invalid stype.  You could also set
>cap to some huge number if it's 0 so your Util would
>basically be 0, or you could do another case in your
>final select, or...

>>-----Original Message-----
>>My ddl:
>>SELECT cnt,CONVERT(varchar(10),OpnDt, 101)AS Dt,
>> SUM(CASE WHEN stype = 'Completed Work' THEN cnt ELSE 0
>>END) AS Comp,
>>        SUM(CASE WHEN stype = 'Process Capability' THEN
>>cnt ELSE 0 END) AS Cap,
>>        SUM(CASE WHEN stype = 'Staged Work' THEN cnt
>ELSE
>>0 END)AS Stag
>>FROM Lean
>>WHERE stype NOT IN ('Defects to RRT')
>>and role = 'setup'
>>and CONVERT(varchar(10), OpnDt, 101)
>between '09/15/2003'
>>and '09/15/2003'
>>GROUP BY  cnt, CONVERT(varchar(10), OpnDt, 101)

>>output:
>>cnt      dt             comp    cap     stag
>>19      09/15/2003      19      0       0
>>25      09/15/2003      0       0       25
>>45      09/15/2003      0       45      0

>>but when I do calculation:
>>SELECT dt,  Round((stag / cap) * 100, 0)  as Util
>>FROM test_vw

>>getting error:
>>Divide by zero error encountered.
>>what should I changed in my first query?

>>Please let me know.

>>thanks

>>.

>.

 
 
 

Divide by zero error encountered

Post by Alejandro Mes » Fri, 31 Oct 2003 22:01:25


Then,

SELECT dt,  ISNULL(Round((stag / NULLIF(cap, 0)) * 100, 0), 0)  as Util
FROM t1_vw

AMB