Link to home
Start Free TrialLog in
Avatar of bapkins
bapkins

asked on

SQL - Divide by Zero Error Encountered

1-(ACOCMP1.CURRSALE.COST / ACOCMP1.CURRSALE.AMOUNT)

Hi All,
I am trying to calculate the margin using an sql view using the above statement.

When i do i get the error Divide by zero error encountered.

Any ideas how to get around this ?

I cannot change the table or data that the view looks at ?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
>Any ideas how to get around this ?
yes: ensure you don't have any rows with AMOUNT = 0 in your query.
SOLUTION
Avatar of DALSOM
DALSOM
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Derokorian
Derokorian
Flag of United States of America image
Or add a where clause, or another that says

WHERE ACOCMP1.CURRSALE.AMOUNT <> 0 AND ACOCMP1.CURRSALE.AMOUNT <> NULL
Avatar of bapkins
bapkins

ASKER

Can you post the whole statement dekorian ? Not sure what you mean but yours seens like a simpler solution.
Avatar of Derokorian
Derokorian
Flag of United States of America image
I can't without seeing your whole query. All you showed was:

1-(ACOCMP1.CURRSALE.COST / ACOCMP1.CURRSALE.AMOUNT)

which by itself is not a query.
ASKER CERTIFIED SOLUTION
Avatar of DALSOM
DALSOM
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bapkins
bapkins

ASKER

Sorry i'm learning all of this as i go !

This is my full query

SELECT     ACOCMP1.CURRSALE.TRANDATE, ACOCMP1.CURRSALE.STKCODE, ACOCMP1.DSINVTRN.DES, ACOCMP1.CURRSALE.STKTYPE, ACOCMP1.CURRSALE.QTY,
                      ACOCMP1.CURRSALE.COST, ACOCMP1.CURRSALE.AMOUNT, ACOCMP1.CURRSALE.AMOUNT - ACOCMP1.CURRSALE.COST AS Profit, ACOCMP1.CURRSALE.CUSTYPE,
                       ACOCMP1.CURRSALE.CUSAC, ACOCMP1.CURRSALE.TILLNO, ACOCMP1.CURRSALE.SESSREF, ACOCMP1.CURRSALE.DEPNO, ACOCMP1.CURRSALE.CCNO,
                      ACOCMP1.CURRSALE.SALESMAN, ACOCMP1.CURRSALE.COUNTRY, ACOCMP1.CURRSALE.NOMAC, ACOCMP1.CURRSALE.TRANREF, ACOCMP1.CURRSALE.ITEMREF,
                      ACOCMP1.CURRSALE.DEPOT, ACOCMP1.CURRSALE.STOCKTRK, ACOCMP1.CURRSALE.INVNO, ACOCMP1.CURRSALE.ASSOCCUS, ACOCMP1.CURRSALE.SITELOC,
                      ACOCMP1.CURRSALE.NLITEM, ACOCMP1.CURRSALE.DUTYAMOUNT, ACOCMP1.CURRSALE.SALETYPE, ACOCMP1.CURRSALE.FRTCHG,
                      ACOCMP1.SALESTAF.SURNAME, ACOCMP1.SALESTAF.FORENAMES, ACOCMP1.STKHEADINFO.DES1, DATEPART(YYYY, ACOCMP1.CURRSALE.TRANDATE)
                      AS CalenderYear, DATEPART(M, ACOCMP1.CURRSALE.TRANDATE) AS CalenderMonth,
                      ACOCMP1.SALESTAF.WRKNUM + ' ' + ACOCMP1.SALESTAF.FORENAMES + ' ' + ACOCMP1.SALESTAF.SURNAME AS SalesPerson,
                      (ACOCMP1.CURRSALE.AMOUNT - ACOCMP1.CURRSALE.COST) / 100 * 1 AS [Commission@1%], (ACOCMP1.CURRSALE.AMOUNT - ACOCMP1.CURRSALE.COST)
                      / 100 * 1.5 AS [Commission@1.5%], (ACOCMP1.CURRSALE.AMOUNT - ACOCMP1.CURRSALE.COST) / 100 * 2 AS [Commission@2%],
                      CASE WHEN CURRSALE.QTY < 0 THEN 'CREDIT' ELSE 'SALE' END AS CreditOrSale, CASE WHEN CURRSALE.QTY < 0 THEN 1 ELSE 0 END AS CreditLineCount,
                      CASE WHEN CURRSALE.QTY > 0 THEN 1 ELSE 0 END AS InvoiceLineCount, 1 - ACOCMP1.CURRSALE.COST / ACOCMP1.CURRSALE.AMOUNT AS Margin
FROM         ACOCMP1.CURRSALE LEFT OUTER JOIN
                      ACOCMP1.SALESTAF ON ACOCMP1.CURRSALE.SALESMAN = ACOCMP1.SALESTAF.WRKNUM LEFT OUTER JOIN
                      ACOCMP1.STKHEADINFO ON ACOCMP1.CURRSALE.STKCODE = ACOCMP1.STKHEADINFO.STKCODE LEFT OUTER JOIN
                      ACOCMP1.DSINVTRN ON ACOCMP1.CURRSALE.ITEMREF = ACOCMP1.DSINVTRN.ITEMREF
WHERE     (DATEPART(YYYY, ACOCMP1.CURRSALE.TRANDATE) = 2011) OR
                      (DATEPART(YYYY, ACOCMP1.CURRSALE.TRANDATE) = 2012)