遇到零除错误 [英] Divide by zero error encountered

问题描述

下面附带的代码无效，它显示一条错误消息

消息8134，等级16，状态1，行2

除以零错误。 listingprice和closingprice某些字段为零是否可以应用任何条件。

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 .

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

推荐答案

使用 NULLIF [ ^ ] - 因为NULL在SQL中传播，它将导致整个计算返回NULL而不是抛出异常。

Use NULLIF[^] - because NULL propagates in SQL, it will cause the whole calculation to return NULL instead of throwing an exception.

ROUND(((OverListedPrice/NUFFIF(listedprice,0))-1)*100,3) as overlistedpercentage

因为除以零在数学上是出于所有实际目的的错误，所以NULL是比任何特定值更好的解决方案。



无论是那个，还是修复你的数据库！ ：笑：

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...

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

这篇关于遇到零除错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！