Solved

SQL  - Divide by zero error encountered.

Posted on 2014-04-22
11
3,464 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

14 Experts available now in Live!

Get 1:1 Help Now