Solved

SQL  - Divide by zero error encountered.

Posted on 2014-04-22
11
3,520 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now