error when dividing Arithmetic overflow error converting int to data type numeric so how to solve it ?

Question

i WORK ON SQL SERVER 2012 i face error
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.

statment generate error

 select m.companyid,m.[Year],m.rev_id,CountMaterials,CountMaterialsConfirment,
FORMAT(case when c.CountMaterialsConfirment IS NULL then 0 ELSE cast(c.CountMaterialsConfirment as decimal(5,2)) / cast(m.CountMaterials as decimal(5,2))  END * 100,'N2')  as totalpercentage  
into ExtractReports.dbo.CountTotalPercentage
 from ExtractReports.dbo.CountDistinctMaterials m with(nolock)
 left join ExtractReports.dbo.CountDistinctMaterialsConfirments c with(nolock) on m.companyid=c.companyid and m.[year]=c.[year] and m.rev_id=c.rev_id

so how to solve issue please ?

Answer 1

Try decimal(35,2) instead of decimal(5,2).

Answer 2

That error indicates " as decimal(5,2))" is not big enough for your number.

Also, assuming they are both INT, you only need to cast one of them to decimal to get decimal places in the divide.

Answer 3

Hi @ahmed salah
Check this:

FORMAT(case when c.CountMaterialsConfirment IS NULL then 0 ELSE cast(c.CountMaterialsConfirment as float) / cast(m.CountMaterials as float)  END * 100,'N2')  as totalpercentage  

Best regards,
LiHong