I have a query that makes a calculation, plus the field "a.costarticles" appears with value zero at times, then it generates a division error by zero, is it possible to pass some default value? Type 1 if it has a value of 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'
You can use the "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'
But just replace 0 by 1 would be a solution or just a "knack"?
Maybe it would be cool to identify why this field is equal to 0 and maybe add a filter in your select to not bring the records they have in the total column for products equal to 0.
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'
SELECT a.totalcustoproducts, a.datanota, 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.type of operation = 'V'
So it's much simpler the NULLIF function returns null if the a.totalcustomproduct is 0 such as the .product products can also be null, we force 0 with the isull a value divided by null returns null and not error. a value divided by 0 of the error.