How to avoid calculation error in SQL Server?

Asked

Viewed 7,358 times

2

I have an appointment that makes a calculation, plus the field "a. totalcustoproducts" appears with zero value in a few moments, then generates a division error by zero, is it possible to pass some standard value? Type 1 if zero?

select a.totalcustoprodutos, a.datanota, a.numeronota, a.cfop,
((a.totalnota / a.totalcustoprodutos) - 1.000) * 100 as TotalMargem   
from TB_C_VENDAS a      
where a.numeronota > 0 and a.tipooperacao = 'V'
sql sql-server
  • 1

    And the correct assume the value of a.totalnota filled if it has no value (0) in a.totalcustoprodutos? The logic is this you want, or are just trying to solve one problem causing another?

  • I need the total cost product products to find the average, I can not use the totalnote. I thank you!

  • So the answers given don’t do what you want, right? What should be done then when you find a zero in the a.totalcustoprodutos? Nothing. does not calculate? Generates error? Takes another value? Uses a completely different formula?

  • Already posted an answer that solved the problem. thank you!

  • If what you said above is right this answer only solves some situations. But I am not the one who will have loss. If you really answer, what you have commented is wrong. Without knowing the real problem, I doubt it will solve correctly. And the problem may be somewhere else. Maybe there shouldn’t even be 0 there. But there is already speculation.

  • You are right, it does not solve everything, it is necessary to analyze the report after more is the only way not to have the error message. I have already identified why the total cost products = 0 plus I need to bring all the records, so it would be a way to avoid the initial error, after the report then it is possible to analyze why the cost is zero. Thanks

Show 1 more comment

3 answers

3

You can make a "CASE". See:

select a.totalcustoprodutos, a.datanota, a.numeronota,a.cfop, ((a.totalnota / (CASE WHEN a.totalcustoprodutos > 0 THEN a.totalcustoprodutos ELSE 1 END)) - 1.000) * 100 as TotalMargem 
from TB_C_VENDAS a
where a.numeronota > 0 and a.tipooperacao = 'V'

  • I appreciate the help!

2

SELECT a.total cost products, a. datanote, a. numeronota, a. cfop ((a.totalnota / NULLIF(isnull(a.totalcustoproducts,0))) - 1.000) * 100 AS Totalmargem, FROM TB_C_VENDAS to WHERE a.numeronota > 0 AND a.typooperation = 'V'

so it’s much simpler the function NULLIF retona null if the a.totalcustoproducts is 0 as a.totalcustoproducts can also be null, we force 0 with isnull a value divided by null returns null and not the error. a value divided by 0 of the error.

1


You can use "Case" and replace "zero" with another number:

Ex:

SELECT a.totalcustoprodutos,
       a.datanota,
       a.numeronota,
       a.cfop,
       ((a.totalnota / (CASE a.totalcustoprodutos WHEN 0 THEN 1 
                        ELSE a.totalcustoprodutos END)) - 1.000) * 100 AS TotalMargem,
FROM TB_C_VENDAS a
WHERE a.numeronota > 0
  AND a.tipooperacao = 'V'

However, would only replacing 0 with 1 be a solution or just a "way"?

Maybe it would be nice to identify why this field is equal to 0 and maybe add a filter in your select to not bring the records that have in the column totalcost products equal to 0.

  • I have already identified why of the total cost products = 0 more I need to bring all the records, so it would be a way to avoid the initial error, after the report then it is possible to analyze why of the zero cost. thanks

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.