SQL - Divide by Zero Error Encountered

bapkins
bapkins used Ask the Experts™
on
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 ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Any ideas how to get around this ?
yes: ensure you don't have any rows with AMOUNT = 0 in your query.
Commented:
Hi,
Try with the result of isnull(@yourvarorfield,0) before do any operation. Try this query :

create table #test_EE(pk int,value1 int,value2 int)

insert into #test_EE values(1,20,10)
insert into #test_EE values(2,20,5)
insert into #test_EE values(3,20,null)
insert into #test_EE values(4,20,0)

select value1,value2,
case when isnull(value2,0) = 0 then Null else
      value1 / value2 end as Coc
from #test_EE

Hope this help you!
Dalsom.
Or add a where clause, or another that says

WHERE ACOCMP1.CURRSALE.AMOUNT <> 0 AND ACOCMP1.CURRSALE.AMOUNT <> NULL
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
Can you post the whole statement dekorian ? Not sure what you mean but yours seens like a simpler solution.
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.
Commented:
Hi, replacing  values you have :

1.Derokorian replacing :

Use ACOCMP1

Select COST,AMOUNT,(COST/AMOUNT) AS PROFITS
FROM CURRSALE
WHERE AMOUNT > 0 AND AMOUNT IS NOT NULL

2. My query replacing :

Use ACOCMP1

Select COST,AMOUNT,
case
when Isnull(AMOUNT,0) then NULL
else
     (COST/AMOUNT)
  End AS PROFITS
FROM CURRSALE

So you are done! First Answer omits all rows with a null or zero Amount,
And the second one does not.  Then choose what better fits you!

Hope this had help you !
Bye,
Dalsom.

Author

Commented:
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)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial