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 ?
bapkinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Any ideas how to get around this ?
yes: ensure you don't have any rows with AMOUNT = 0 in your query.
0
DALSOMCommented:
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.
0
DerokorianCommented:
Or add a where clause, or another that says

WHERE ACOCMP1.CURRSALE.AMOUNT <> 0 AND ACOCMP1.CURRSALE.AMOUNT <> NULL
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

bapkinsAuthor Commented:
Can you post the whole statement dekorian ? Not sure what you mean but yours seens like a simpler solution.
0
DerokorianCommented:
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.
0
DALSOMCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bapkinsAuthor 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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.