Error when calling the report

Navigation
1

I have an error below, when I call a report, which has a query in the database, I can not resolve it.

Takealookattheresultyouhavenothingwithzero

Followthecode:

SELECTSD.D2_DOC,SA.A1_NREDUZ,SB.B1_DESC,m.nm_mes,SD.D2_QUANT,SD.D2_PRCVEN,(SD.D2_CUSTO1/SD.D2_QUANT)ASCUSTO,((SD.D2_PRCVEN-(SD.D2_CUSTO1/SD.D2_QUANT))/SD.D2_PRCVEN)*100ASMARGEMFROMSD2010ASSDleftouterjoinmesmonm.cd_mes=month(SD.D2_EMISSAO)INNERJOINSB1010ASSBWITH(NOLOCK)ONSB.B1_COD=SD.D2_CODINNERJOINSA1010ASSAWITH(NOLOCK)ONSA.A1_COD=SD.D2_CLIENTEWHERESD.D_E_L_E_T_<>'*'ANDSD.D2_CFIN('5102','5117','5119','5123','5124','5403','5405','6102','6108','6110','6117','6119','6123','6124','6403','6405','7102')ANDYEAR(SD.D2_EMISSAO)='2017'ANDSB.B1_DESC='"CORN PO4 PH ""B"""' AND m.nm_mes = 'Janeiro'
    
asked by anonymous 12.04.2017 / 14:18

1 answer

1

You should change your query and make sure that in these two columns you do not consider the value zero. Not to occur:

  

Zero division error.

I proposed two solutions:

Solution 1 It was just append in the where to filter any one that is 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 converting 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'

Update

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'
    
12.04.2017 / 18:03
Script to show capital of selected state? How to get back to the top of an Activity?