Divide by zero error encountered.

Question

hello...

I have one error and i dont know what it is.. :S

 

my code is:

 

SELECT  1 Ordem, ref,design,a,b,c,qtt,Un, CAST(MAX(valor1) as decimal(18,2)) as Valor1, CAST(MAX(valor2) as decimal(18,2)) as Valor2, CAST(MAX(valor3) as decimal(18,2)) as Valor3     

from
  
(select bi.ref as ref,bi.design as design,bi.qtt as qtt,bi.unidade as Un,   
  
(select LEFT(bo.nome,11) FROM bo (NOLOCK) WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano = 10) as a,    
(select LEFT(bo.nome,11) FROM bo (NOLOCK) WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano = 10+1) as b,    
(select LEFT(bo.nome,11) FROM bo (NOLOCK) WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano = 12)as c,   
  
CASE WHEN bo.obrano = 10   THEN  (bi.ettdeb/bi.qtt) END as valor1,   
CASE WHEN bo.obrano = 10+1 THEN  (bi.ettdeb/bi.qtt) END as valor2,   
CASE WHEN bo.obrano = 12   THEN  (bi.ettdeb/bi.qtt) END as valor3   

FROM bo (NOLOCK) inner join bi (NOLOCK) on bo.bostamp=bi.bostamp   
  
WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano BETWEEN 10 AND 12    
  
group by bi.ref,bi.design,bi.qtt,bo.obrano, bi.ettdeb,bi.unidade) as test
  
group by ref,design,a,b,c,qtt,Un

error is:

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

Can help?? Taks in advace..

Answer 1

sory..

Error is:

 

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

Warning: Null value is eliminated by an aggregate or other SET operation.

 

Thanks

Answer 2

the column qtt has value 0

check in the table

---

Life is a race,tez nahi bhagoge to log kuchalke agey niklenge!

Answer 3

Hi,

Its because there is some calculation where you used "bi.qtt"

It might possible that this field comes  "0", so SQl raise an error.

 

Thanks,

---

Tejas Shah
SQL YOGA

Answer 4

Hi,

Its because there is some calculation where you used "bi.qtt"

It might possible that this field comes  "0", so SQl raise an error.

 

Thanks,

---

Tejas Shah
SQL YOGA

Answer 5

>WHEN bo.obrano = 12 THEN (bi.ettdeb / bi.qtt)

How do you want to handle it when qtt is zero?

You can use the CASE function to avoid zero divide for example. Demo follows.

SELECT ProductID,
CostRatio = 100.0 * (StandardCost/
CASE WHEN ListPRice > 0 THEN ListPrice ELSE 1.0 END)
FROM AdventureWorks2008.Production.Product

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 6

Excelent...

Thanks.. ;)