OCI-22053: 溢出错误

oracle有一种错误OCI-22053: 溢出错误,一种情况就是小数点后太多位数。用round((1-SUM(OutNoQualifiedNum)/SUM(OutPeccancyNum)),5)函数保留小数点后5位

例如:

SELECT SUM(CASE WHEN OutPeccancyNum=0 THEN 1 ELSE 0 END) AS InPersonNum,
SUM(CASE WHEN InPeccancyNum=0 THEN 1 ELSE 0 END) AS OutPersonNum,
SUM(InPeccancyNum) AS InPeccancyNum,SUM(InNoQualifiedNum) AS InNoQualifiedNum,
(CASE WHEN SUM(InPeccancyNum)=0 THEN 0 ELSE round((1-SUM(InNoQualifiedNum)/SUM(InPeccancyNum)),5) END) AS InQualifiedRate,
SUM(OutPeccancyNum) AS OutPeccancyNum,
SUM(OutNoQualifiedNum) AS OutNoQualifiedNum,
(CASE WHEN SUM(OutNoQualifiedNum)=0 THEN 0 ELSE round((1-SUM(OutNoQualifiedNum)/SUM(OutPeccancyNum)),5) END) AS OutQualifiedRate,
SquadronID
FROM REP_PersonPeccancyByMonth  Where 1=1  and StatisticsMonth='2015-04'
GROUP BY SquadronID

posted @ 2015-11-05 15:31  webzhang  阅读(988)  评论(0编辑  收藏  举报