May 14, 2020 at 5:30 pm
I am getting an Arithmetic Overflow error on this field, suggestions on a fix?
CAST(ROUND(CAST(COALESCE(r.ActualAllowed * 100.0 / NULLIF(r.Allowed, 0), 0) AS FLOAT), 1) AS VARCHAR(10)) + '%' AS FeeSchedulePercentDiff
May 14, 2020 at 7:30 pm
do a query on the max and min values for r.ActualAllowed - that * 100.0 is most likely resulting on a bigger value than what a decimal can hold.
so casting it to float before doing the calculation could sort your issue
May 14, 2020 at 8:37 pm
To add to Frederico_fonseca's response - when I hit snags like that, my first step is to take the innermost portion of the troublesome query section and break it out. In this case, I'd start by taking "r.ActualAllowed*100.0" and see if that works. If it gives no error, then add in the division. If we are still good, then add on the COALESCE. No error still, put on the CAST. Still no errors, add on the ROUND, and finally add on the CAST. At some point in that you will get the error and you can determine which portion is causing the overflow. It is very unlikely to be the CAST to VARCHAR, but it could be the r.ActualAllowed * 100.0 or it could be the casting to FLOAT. Or it could be the division (if Allowed is less than 1 but more than 0).
We cannot see your datatypes, or your data, so it is entirely guesswork on our side. It might even be that this portion of your query is fine and it is something different causing the overflow.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply