locked
Divide by zero error encountered. RRS feed

  • 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..
    Tuesday, March 30, 2010 10:27 AM

Answers

  • >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
    • Marked as answer by Night_devil Tuesday, March 30, 2010 10:55 AM
    Tuesday, March 30, 2010 10:38 AM

All replies

  • 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

    Tuesday, March 30, 2010 10:28 AM
  • the column qtt has value 0

    check in the table


    Life is a race,tez nahi bhagoge to log kuchalke agey niklenge!
    Tuesday, March 30, 2010 10:30 AM
  • 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
    Tuesday, March 30, 2010 10:31 AM
  • 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
    Tuesday, March 30, 2010 10:32 AM
  • >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
    • Marked as answer by Night_devil Tuesday, March 30, 2010 10:55 AM
    Tuesday, March 30, 2010 10:38 AM
  • Excelent...

    Thanks.. ;)

    Tuesday, March 30, 2010 10:55 AM