Text: Arithmetic overflow error converting varchar to data type numeric

Epicor ERP 10
1

Hi All,
i am creating a BAQ with a calculated column to show carton number in a format “1/557 - 556/557 & 557/557 @ 6.0” (meaning total carton number 557, with the last carton number 557 with loose quantity of 6)

Below is my calculated column formula in BAQ, however, i get error messages on this column.
Error: Text: Arithmetic overflow error converting varchar to data type numeric.

(case when cast(SubQuery1.UD100A_NoofPcsPerCarton_c as varchar(15)) > 0.00
		then (case when cast(SubQuery1.OrderDtl_IUM as varchar(10)) = 'GRS'
						 then (case when cast(Loose_Qty as varchar(15)) >= 1.00
								      then cast(('1/' + Tot_Carton +  ' - ' + Tot_Carton - 1 + '/' + Tot_Carton +  ' & ' + Tot_Carton + '/' + Tot_Carton + ' @ ' + Loose_Qty) as varchar(30))
											else cast(('1/' + Tot_Carton +  ' - ' + Tot_Carton + '/' + Tot_Carton) as varchar(30))
									end)   
						 else (case when cast(Loose_Qty as varchar(15)) >= 1.00
                      then cast(('1/' + Tot_Carton +  ' - ' + Tot_Carton-1 + '/' + Tot_Carton +  ' & ' + Tot_Carton +  '/' + Tot_Carton + ' @ ' + Loose_Qty) as varchar(40))
                      else cast(('1/' + Tot_Carton +  ' - ' + Tot_Carton +  '/' + Tot_Carton) as varchar(30))
                   end)
				 end) 
    else 0 
end)
2

I dont even know where to begin, like here you are casting to a varchar and then trying to do >= against a numeric.

Also in your else you are returning 0 (a int).

For starters I would probably not cast in the areas where you are comparing it against =, <> or >= or <= or > or <

cast(SubQuery1.OrderDtl_IUM as varchar(10)) = 'GRS'

This one should be ok, but you dont need a cast. Depending on your SubQuery if its a Left Join you might need to use ISNULL().

1 Like
3

Hi Hasokeric,
i try to remove all the casting on the comparison statement as what you suggest. but i still get the same error message. in fact initially the casting is not in the comparison statement. i put it in to see it resolved the error or not.

Below i amended the formula as below:
(case when SubQuery1.UD100A_NoofPcsPerCarton_c > 0.00
then (case when SubQuery1.OrderDtl_IUM = ‘GRS’
then (case when Loose_Qty >= 1.00
then cast((‘1/’ + Tot_Carton + ’ - ’ + Tot_Carton - 1 + ‘/’ + Tot_Carton + ’ & ’ + Tot_Carton + ‘/’ + Tot_Carton + ’ @ ’ + Loose_Qty) as varchar(30))
else cast((‘1/’ + Tot_Carton + ’ - ’ + Tot_Carton + ‘/’ + Tot_Carton) as varchar(30))
end)
else (case when Loose_Qty >= 1.00
then cast((‘1/’ + Tot_Carton + ’ - ’ + Tot_Carton-1 + ‘/’ + Tot_Carton + ’ & ’ + Tot_Carton + ‘/’ + Tot_Carton + ’ @ ’ + Loose_Qty) as varchar(40))
else cast((‘1/’ + Tot_Carton + ’ - ’ + Tot_Carton + ‘/’ + Tot_Carton) as varchar(30))
end)
end)
else 0
end)

4

Inside the cast(), you’re combining numbers and strings. Cast each number to a stirng individually. Like

'1/' + cast(Tot_Carton as varchar(40)) + ' - ' + cast(Tot_Carton-1 as varchar(40)) + '/' + cast(Tot_Carton as varchar(40)) + ' & ' + cast(Tot_Carton as varchar(40)) + '/' + cast(Tot_Carton as varchar(40)) + ' @ ' + cast(Loose_Qty as varchar(40))

Note that I didnt check your logic at all, just showed that the cast() should only convert the numbers to strings before combining with other strings.

5

Hi Calvin,
Thanks for your guidance. It resolved the problem now.