Error Calling Up Report

Question

I have an error below, when I call a report, which has a query in the database, I am not able to solve.

Give a look at the result has nothing to zero

Follows the code:

SELECT
  SD.D2_DOC,
  SA.A1_NREDUZ,
  SB.B1_DESC, 
  m.nm_mes, 
  SD.D2_QUANT,
  SD.D2_PRCVEN,
  (SD.D2_CUSTO1 / SD.D2_QUANT) AS CUSTO,
  ((SD.D2_PRCVEN - (SD.D2_CUSTO1 / SD.D2_QUANT)) / SD.D2_PRCVEN) * 100 AS MARGEM
  FROM  SD2010 AS SD 
  left outer join mes m on m.cd_mes = month(SD.D2_EMISSAO) 
  INNER JOIN SB1010 AS SB WITH (NOLOCK) ON SB.B1_COD = SD.D2_COD
  INNER JOIN SA1010 AS SA WITH (NOLOCK) ON SA.A1_COD = SD.D2_CLIENTE 
  WHERE SD.D_E_L_E_T_ <> '*' AND SD.D2_CF IN ('5102', '5117', '5119', '5123', '5124', '5403', '5405', '6102', '6108', '6110', '6117', '6119', '6123', '6124', '6403', '6405', '7102') 
  AND YEAR(SD.D2_EMISSAO) = '2017' AND SB.B1_DESC = '"CORN PO4 PH ""B"""' AND m.nm_mes = 'Janeiro'

Answer 1

You should change this your query and make sure that in these two columns do not consider the value zero.
To avoid the:

Division error by zero.

I proposed two solutions:

Solution 1 Just added to Where to filter anyone who’s zero

SELECT
    SD.D2_DOC,
    SA.A1_NREDUZ,
    SB.B1_DESC, 
    M.NM_MES, 
    SD.D2_QUANT,
    SD.D2_PRCVEN,
    (SD.D2_CUSTO1 / SD.D2_QUANT) AS CUSTO,
    ((SD.D2_PRCVEN - (SD.D2_CUSTO1 / SD.D2_QUANT)) / SD.D2_PRCVEN) * 100 AS MARGEM
FROM  SD2010 AS SD 
LEFT OUTER JOIN MES M ON M.CD_MES = MONTH(SD.D2_EMISSAO) 
INNER JOIN SB1010 AS SB WITH (NOLOCK) ON SB.B1_COD = SD.D2_COD
INNER JOIN SA1010 AS SA WITH (NOLOCK) ON SA.A1_COD = SD.D2_CLIENTE 
WHERE SD.D_E_L_E_T_ <> '*' AND SD.D2_CF IN ('5102', '5117', '5119', '5123', '5124', '5403', '5405', '6102', '6108', '6110', '6117', '6119', '6123', '6124', '6403', '6405', '7102') 
    AND YEAR(SD.D2_EMISSAO) = '2017' AND SB.B1_DESC = '"CORN PO4 PH ""B"""' AND M.NM_MES = 'JANEIRO'
    AND  SD.D2_QUANT > 0 AND SD.D2_PRCVEN > 0 --SOLUÇÃO PROPOSTA 1

Solution 2
A case when to convert zero to null

SELECT
    SD.D2_DOC,
    SA.A1_NREDUZ,
    SB.B1_DESC, 
    M.NM_MES, 
    SD.D2_QUANT,
    SD.D2_PRCVEN,
    (SD.D2_CUSTO1 / CASE WHEN SD.D2_QUANT = 0 THEN NULL ELSE SD.D2_QUANT END) AS CUSTO, --SOLUÇÃO PROPOSTA 2
    ((SD.D2_PRCVEN - (SD.D2_CUSTO1 / CASE WHEN SD.D2_QUANT = 0 THEN NULL ELSE SD.D2_QUANT END)) / CASE WHEN SD.D2_PRCVEN = 0 THEN NULL ELSE SD.D2_PRCVEN END) * 100 AS MARGEM  --SOLUÇÃO PROPOSTA 2
FROM  SD2010 AS SD 
LEFT OUTER JOIN MES M ON M.CD_MES = MONTH(SD.D2_EMISSAO) 
INNER JOIN SB1010 AS SB WITH (NOLOCK) ON SB.B1_COD = SD.D2_COD
INNER JOIN SA1010 AS SA WITH (NOLOCK) ON SA.A1_COD = SD.D2_CLIENTE 
WHERE SD.D_E_L_E_T_ <> '*' AND SD.D2_CF IN ('5102', '5117', '5119', '5123', '5124', '5403', '5405', '6102', '6108', '6110', '6117', '6119', '6123', '6124', '6403', '6405', '7102') 
    AND YEAR(SD.D2_EMISSAO) = '2017' AND SB.B1_DESC = '"CORN PO4 PH ""B"""' AND M.NM_MES = 'JANEIRO'

Updating

Solution 3 - Ideal way to solve

SELECT  SD.D2_DOC,
        SA.A1_NREDUZ,
        SB.B1_DESC, 
        M.NM_MES, 
        SD.D2_QUANT,
        SD.D2_PRCVEN,
        CASE WHEN SD.D2_QUANT = 0 THEN 0 ELSE SD.D2_CUSTO1 / SD.D2_QUANT END AS CUSTO, --SOLUÇÃO PROPOSTA 3    
        CASE WHEN SD.D2_QUANT = 0 OR SD.D2_PRCVEN = 0 THEN 0 ELSE ((SD.D2_PRCVEN - (SD.D2_CUSTO1 / SD.D2_QUANT)) / SD.D2_PRCVEN) * 100 END AS MARGEM  --SOLUÇÃO PROPOSTA 3
FROM  SD2010 AS SD 
LEFT OUTER JOIN MES M ON M.CD_MES = MONTH(SD.D2_EMISSAO) 
INNER JOIN SB1010 AS SB WITH (NOLOCK) ON SB.B1_COD = SD.D2_COD
INNER JOIN SA1010 AS SA WITH (NOLOCK) ON SA.A1_COD = SD.D2_CLIENTE 
WHERE SD.D_E_L_E_T_ <> '*' AND SD.D2_CF IN ('5102', '5117', '5119', '5123', '5124', '5403', '5405', '6102', '6108', '6110', '6117', '6119', '6123', '6124', '6403', '6405', '7102') 
        AND YEAR(SD.D2_EMISSAO) = '2017' AND SB.B1_DESC = '"CORN PO4 PH ""B"""' AND M.NM_MES = 'JANEIRO'