Click here to Skip to main content
15,076,991 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
below attached code is not working , its showing one error message
"Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered." "listedprice" and "closingprice' some fields are Zero is it possible to apply any condition .
SQL
select *, round(((OverListedPrice/listedprice)-1)*100,3) as overlistedpercentage,
 Round(((NetGainorLoss/closingprice)-1*100),3) as netgainpercentage from
 (select c.BranchName as brname,c.BranchCode as brcode,c.listedprice as listedprice,c.ClosingPrice as closingprice,d.CurrentPrice,
cast(d.CurrentPrice as decimal)-cast(c.ListedPrice as decimal) as OverListedPrice,
 cast(d.CurrentPrice as decimal)-cast(c.ClosingPrice as decimal) as NetGainorLoss
   from(select a.branchname,a.branchcode,a.listedprice,b.closingprice from
 (select (BREMPRONAME)as BranchName,(BREMPROCODE)as BranchCode, (price) as
 ListedPrice from Dataupload  where  Date='05/Jul/2015'  and REGIONCODE='025') a
left join
 (select bremprocode,(price)as ClosingPrice from Dataupload where REGIONCODE='025'
 and MONTH(date)=MONTH(GETDATE()-1)and day(date)=day(getdate()-1) and time='7 PM') b
 on a.branchcode=b.bremprocode) c left join
 ( select bremprocode,(PRICE)as CurrentPrice from
 Dataupload  where  id in( select id  from(
 select  MAX(D.ID)as id,D.BREMPROCODE from dataupload D
 where D.BREMPROCODE IN(SELECT BREMPROCODE FROM Dataupload
 WHERE REGIONCODE='025')
 GROUP BY D.BREMPROCODE)x))d
 on c.branchcode=d.bremprocode) p
Posted

Use NULLIF[^] - because NULL propagates in SQL, it will cause the whole calculation to return NULL instead of throwing an exception.
SQL
ROUND(((OverListedPrice/NUFFIF(listedprice,0))-1)*100,3) as overlistedpercentage

Because division by zero is mathematically an error for all practical purposes, NULL is a better solution than any specific value.

Either that, or fix your Database! :laugh:
   
Hi,

Check this...

SQL
select *, 
CASE WHEN listedprice=0 THEN round(((OverListedPrice)-1)*100,3) ELSE round(((OverListedPrice/listedprice)-1)*100,3) END as overlistedpercentage,
CASE WHEN closingprice=0 THEN Round(((NetGainorLoss)-1*100),3) ELSE Round(((NetGainorLoss/closingprice)-1*100),3) as netgainpercentage 
from
(
select c.BranchName as brname,
c.BranchCode as brcode,
c.listedprice as listedprice,
c.ClosingPrice as closingprice,
d.CurrentPrice,
cast(d.CurrentPrice as decimal)-cast(c.ListedPrice as decimal) as OverListedPrice,
cast(d.CurrentPrice as decimal)-cast(c.ClosingPrice as decimal) as NetGainorLoss
from
(
select a.branchname,
a.branchcode,
a.listedprice,
b.closingprice 
from
(
select (BREMPRONAME)as BranchName,
(BREMPROCODE)as BranchCode, 
(price) as ListedPrice 
from Dataupload  
where  Date='05/Jul/2015'  and REGIONCODE='025'
)a
left join
(
select 
bremprocode,
(price)as ClosingPrice 
from 
Dataupload 
where 
REGIONCODE='025'
and 
MONTH(date)=MONTH(GETDATE()-1)
and day(date)=day(getdate()-1) 
and time='7 PM'
)b
on a.branchcode=b.bremprocode) 
c left join
( 
select bremprocode,
(PRICE)as CurrentPrice 
from
Dataupload  
where  
id in(select id  from( select  MAX(D.ID)as id,D.BREMPROCODE from dataupload D
 where D.BREMPROCODE IN(SELECT BREMPROCODE FROM Dataupload
 WHERE REGIONCODE='025')
 GROUP BY D.BREMPROCODE)x))d
 on c.branchcode=d.bremprocode) p


Hope this will help you.

Cheers
   
Comments
Unni R 14-Jul-15 1:41am
   
showing another eror
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '15.6' to data type int.
Magic Wonder 14-Jul-15 1:52am
   
Kindly fix your table structure w.r.t. data stored. Fixing will remove all such errors.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900