Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL  - Divide by zero error encountered.

Posted on 2014-04-22
11
Medium Priority
?
3,894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 66

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 1500 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 66

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

705 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