Solved

SQL  - Divide by zero error encountered.

Posted on 2014-04-22
11
3,632 Views
Last Modified: 2014-04-24
HI Experts,

I am running some SQL that is looking for a record set based on a transaction number.  When running this and it is adding and multiplying it works fine, however, when dividing i get an error


Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

The problem is when I divide

I have tried dividing by values where I know there is values both greater and less than 0 and still get the same result.

I would be grateful if you could help me refine the code so that I can continue to create my report(s)

select
th.TradeNumber, th.tradedate, td.SYMBOL ,sum(tp.tradeccy1amount), 
tp.ccyid1 as CCY1, sum(tp2.TRADECCY1AMOUNT), tp2.ccyid1 as CCY2, tp.TRADERATE, td.VALUEDATE, 
c.CustomerID,c.LEGALNAME, c.CUSTOMERORIGINTYPE, sum(tp.VALUATIONSPOTAMOUNT,

(select case when tr.REVALUATIONCCYID = 'USD' then sum(tr.TODAYENDINGVALUE)-sum(tr.TODAYSTARTINGVALUE)
when ri.terms = 'M' then sum(tr.TODAYENDINGVALUE*ri.bid)-sum(tr.TODAYSTARTINGVALUE*ri.bid)
when ri.terms = 'D' then sum(tr.TODAYENDINGVALUE/ri.bid)+sum(tr.TODAYSTARTINGVALUE/ri.bid)
end 
) as PL

from dbo.tradeheader  th 
inner join dbo.tradedetail td on th.GUID = td.TRADEHEADERGUID 
inner join dbo.tradeposition tp on th.GUID = tp.TRADEHEADERGUID and tp.positionID = 1
inner join dbo.tradeposition tp2 on th.GUID = tp2.TRADEHEADERGUID and tp2.positionID = 2
left outer join dbo.TRADEOPTIONDETAIL topd on th.guid = topd.tradeheaderguid 
left outer join dbo.customer c on td.CUSTOMERID = c.customerID 
inner join dbo.TRADEREVALUATION tr on tr.TRADEHEADERGUID = th.guid 
join RATE_I ri on ri.SYMBOL = td.symbol 
--where th.TRADENUMBER = '511001022' and th.entityID = 'IFEL'
where th.entityID = 'IFEL'
and ri.TERMS = 'D'
group by
th.TradeNumber, th.tradedate, td.SYMBOL , 
tp.ccyid1, tp2.ccyid1, tp.TRADERATE, td.VALUEDATE, c.CustomerID,c.LEGALNAME, c.CUSTOMERORIGINTYPE,tr.REVALUATIONCCYID, ri.terms, 
tr.TODAYENDINGVALUE, tr.TODAYSTARTINGVALUE, ri.bid, tr.REVALUATIONCCYID

Open in new window

0
Comment
Question by:SimonPrice33
  • 6
  • 3
  • 2
11 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 40015094
I just wrapped the case when then portion that divides with another case. the case I input checks to see if ri.bid = 0 or is null. if either, it returns nothing, otherwise it performs the division.

select
th.TradeNumber, th.tradedate, td.SYMBOL ,sum(tp.tradeccy1amount), 
tp.ccyid1 as CCY1, sum(tp2.TRADECCY1AMOUNT), tp2.ccyid1 as CCY2, tp.TRADERATE, td.VALUEDATE, 
c.CustomerID,c.LEGALNAME, c.CUSTOMERORIGINTYPE, sum(tp.VALUATIONSPOTAMOUNT,

(select case when tr.REVALUATIONCCYID = 'USD' then sum(tr.TODAYENDINGVALUE)-sum(tr.TODAYSTARTINGVALUE)
when ri.terms = 'M' then sum(tr.TODAYENDINGVALUE*ri.bid)-sum(tr.TODAYSTARTINGVALUE*ri.bid)
when ri.terms = 'D' then case when Isnull(ri.bid,0) = 0 THEN null ELSE sum(tr.TODAYENDINGVALUE/ri.bid)+sum(tr.TODAYSTARTINGVALUE/ri.bid) end
end 
) as PL

from dbo.tradeheader  th 
inner join dbo.tradedetail td on th.GUID = td.TRADEHEADERGUID 
inner join dbo.tradeposition tp on th.GUID = tp.TRADEHEADERGUID and tp.positionID = 1
inner join dbo.tradeposition tp2 on th.GUID = tp2.TRADEHEADERGUID and tp2.positionID = 2
left outer join dbo.TRADEOPTIONDETAIL topd on th.guid = topd.tradeheaderguid 
left outer join dbo.customer c on td.CUSTOMERID = c.customerID 
inner join dbo.TRADEREVALUATION tr on tr.TRADEHEADERGUID = th.guid 
join RATE_I ri on ri.SYMBOL = td.symbol 
--where th.TRADENUMBER = '511001022' and th.entityID = 'IFEL'
where th.entityID = 'IFEL'
and ri.TERMS = 'D'
group by
th.TradeNumber, th.tradedate, td.SYMBOL , 
tp.ccyid1, tp2.ccyid1, tp.TRADERATE, td.VALUEDATE, c.CustomerID,c.LEGALNAME, c.CUSTOMERORIGINTYPE,tr.REVALUATIONCCYID, ri.terms, 
tr.TODAYENDINGVALUE, tr.TODAYSTARTINGVALUE, ri.bid, tr.REVALUATIONCCYID

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40015103
In general, whenever you have a divide expression you'll have to test for 0 and handle it
-- e.g., a / b
CASE b WHEN 0 THEN 0 ELSE a / b END as your_column

Open in new window

So looking at the below code, if ANY ri.bid value is zero, it'll throw an error.
when ri.terms = 'D' then sum(tr.TODAYENDINGVALUE/ri.bid)+sum(tr.TODAYSTARTINGVALUE/ri.bid)

Open in new window

So try...
when ri.terms = 'D' then 
   sum(CASE ri.bid WHEN 0 THEN 0 ELSE tr.TODAYENDINGVALUE/ri.bidEND) +                                 sum(CASE ri.bid WHEN 0 THEN 0 ELSE tr.TODAYSTARTINGVALUE/ri.bid END)

Open in new window


Btw the above doesn't handle NULL values, so if a NULL may be in your data you'll have to change it to...
when ri.terms = 'D' then 
   sum(CASE ISNULL(ri.bid,0) WHEN 0 THEN 0 ELSE tr.TODAYENDINGVALUE/ri.bidEND) +                                 sum(CASE ISNULL(ri.bid,0) WHEN 0 THEN 0 ELSE tr.TODAYSTARTINGVALUE/ri.bid END)

Open in new window

0
 

Author Comment

by:SimonPrice33
ID: 40015148
Hey Dulton, We tried that before and still get the same issue
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 6

Expert Comment

by:Dulton
ID: 40015167
If ri.bid is a decimal or float it may not be = 0, but when you divide it, it gets cast as an int and rounds to 0.

Try what I proposed, but include a cast to int during the check. That should eliminate any implicit rounding.

case when Cast(Isnull(ri.bid,0) AS INT) = 0 THEN null ELSE sum(tr.TODAYENDINGVALUE/ri.bid)+sum(tr.TODAYSTARTINGVALUE/ri.bid) end
0
 

Author Comment

by:SimonPrice33
ID: 40015215
ive started working with a date range and am not getting the issue....

this is a dev system so i am wondering if its simply just bum data but does need to work if it happens in a live environment.
0
 

Author Comment

by:SimonPrice33
ID: 40015236
Dulton,

still the same issue could be ive not put it in right \ understood what you meant.. this is where i have put it

(select case when tr.REVALUATIONCCYID = 'USD' then sum(tr.TODAYENDINGVALUE)-sum(tr.TODAYSTARTINGVALUE)
when ri.terms = 'M' then sum(tr.TODAYENDINGVALUE*ri.bid)-sum(tr.TODAYSTARTINGVALUE*ri.bid)
when ri.terms = 'D' then case when Cast(Isnull(ri.bid,0) AS INT) = 0 THEN null ELSE sum(tr.TODAYENDINGVALUE/ri.bid)+sum(tr.TODAYSTARTINGVALUE/ri.bid) end
 end

) as PL
0
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 40015342
You've added it to the correct place.

I'm beginning to wonder if your query needs broken up into smaller parts, cte's, etc. I see you are grouping by ri.bid and ri.type, but you've restricted ri.type in the where clause to "D". By doing that, it eliminates the portion of the case statement where ri.type="M".
I'm kind of wondering what the results would be when ri.bid = 0 and since you're grouping by that, what happens..... I like to think the case statement would handle that, but apparently not.

I'm guessing that was done for test purposes, but it still may benefit greatly from being split up into smaller parts.... I would do your case and math parts on separate query levels than your aggregates (SUM()). It may be possible to do it all together, but I'd have to see your data and expected results to understand if and how it would be possible.
0
 

Author Comment

by:SimonPrice33
ID: 40015357
we're trying this now...

this does bring data back, but we are uncertain that its correct :/

select dave.TradeNumber, dave.tradedate, dave.SYMBOL ,sum(dave.ccy1ammount) as CCY1Amount, 
dave.CCY1, sum(dave.ccy2ammount) as CCY2Amount, dave.CCY2, dave.TRADERATE, dave.VALUEDATE, 
dave.CustomerID, dave.LEGALNAME, dave.CUSTOMERORIGINTYPE, sum(dave.VALUATIONSPOTAMOUNT) as ValuationSpotAmount, 
dave.TODAYENDINGVALUE, dave.TODAYSTARTINGVALUE, dave.REVALUATIONCCYID, dave.BID, dave.terms, sum(dave.PL) as PL 
from (

select th.TradeNumber, th.tradedate, td.SYMBOL ,tp.tradeccy1amount as ccy1ammount, 
tp.ccyid1 as CCY1, tp2.TRADECCY1AMOUNT as ccy2ammount, tp2.ccyid1 as CCY2, tp.TRADERATE, td.VALUEDATE, 
c.CustomerID,c.LEGALNAME, c.CUSTOMERORIGINTYPE, tp.VALUATIONSPOTAMOUNT, 
tr.TODAYENDINGVALUE, tr.TODAYSTARTINGVALUE, tr.REVALUATIONCCYID, ri.BID, ri.terms,

(select case when tr.REVALUATIONCCYID = 'USD' then tr.TODAYENDINGVALUE - tr.TODAYSTARTINGVALUE
when ri.terms = 'M' then tr.TODAYENDINGVALUE*ri.bid - tr.TODAYSTARTINGVALUE*ri.bid
when ri.terms = 'D' and ABS(ri.bid) > 0 then tr.TODAYENDINGVALUE/ri.bid + tr.TODAYSTARTINGVALUE/ri.bid
--when ri.terms = 'D' then sum(tr.TODAYENDINGVALUE/ri.bid + tr.TODAYSTARTINGVALUE/ri.bid)
--when ri.terms = 'D' and ABS(ri.bid) > 0 then 999999999
--when ri.TERMS = 'D'and ABS(ri.bid) > 0 then sum(tr.TODAYSTARTINGVALUE/ri.bid)
else 99999999999
end 
) as PL

from dbo.tradeheader  th 
inner join dbo.tradedetail td on th.GUID = td.TRADEHEADERGUID 
inner join dbo.tradeposition tp on th.GUID = tp.TRADEHEADERGUID and tp.positionID = 1
inner join dbo.tradeposition tp2 on th.GUID = tp2.TRADEHEADERGUID and tp2.positionID = 2
left outer join dbo.TRADEOPTIONDETAIL topd on th.guid = topd.tradeheaderguid 
left outer join dbo.customer c on td.CUSTOMERID = c.customerID 
inner join dbo.TRADEREVALUATION tr on tr.TRADEHEADERGUID = th.guid 
join RATE_I ri on ri.SYMBOL = td.symbol 
--where th.TRADENUMBER = '511001022' and th.entityID = 'IFEL'
where th.entityID = 'IFEL'

) as dave
group by
dave.TradeNumber, dave.tradedate, dave.SYMBOL , 
dave.CCY1, dave.CCY2, dave.TRADERATE, dave.VALUEDATE, dave.CustomerID,dave.LEGALNAME, dave.CUSTOMERORIGINTYPE,dave.REVALUATIONCCYID, dave.terms, 
dave.TODAYENDINGVALUE, dave.TODAYSTARTINGVALUE, dave.bid, dave.REVALUATIONCCYID


--join (select TRADEHEADERGUID, TODAYENDINGVALUE, TODAYSTARTINGVALUE, REVALUATIONCCYID from TRADEREVALUATION where TODAYENDINGVALUE 
--				 is not null and TODAYSTARTINGVALUE is not null) tr on tr.TRADEHEADERGUID = th.guid 

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40015615
>but we are uncertain that its correct :/
And experts here can't help you with that, as we're not connected to your data source, so you'll have to flush that out on your own.
0
 

Author Comment

by:SimonPrice33
ID: 40016805
hey Jim

I know that you cant help me with the data its self, but the division by zerio error is my main concern...
0
 

Author Closing Comment

by:SimonPrice33
ID: 40019468
its working in the way its not returning the error any more
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question