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