All Forums Database
john_89 2 posts Joined 02/15
25 Feb 2015
overflow occured computing an expression involving

Hi All,
   I am facing some challenge in dividing two decimal numbers and updating a target decimal column.
one important thing there few zeros are there in source coulmns(actual_amt and amt_allowed)
actual_amt decimal(9,2)
amt_allowed decimal(9,2)
percent_col decimal(5,2)
expected is
5679.89/4569.67*100 --> 124.29
I am using the below update statement.
update dbname.tgt_table_name
percent_col= 100*(cast(actual_amt/amt_allowed) as decimal(9,2))
cast(actual_amt as decimal(9,2) <> cast(0 as decimal(9,2))
cast(amt_allowed as decimal(9,2) <> cast(0 as decimal(9,2))
I am getting 2617: overflow occurred computing an expression involving amt_allowed.
Kindly help me on this.

MaximeV 19 posts Joined 11/13
26 Feb 2015

hi ,
The statement you provided should not work because it has some parenthesis issues;;but considering you get a numeric overflow error, you probably got it right..
I guess you have a percentage higher than 999.99% that does not fit on your decimal(5,2)
check your max value for actual_amt/amt_allowed*100

john_89 2 posts Joined 02/15
26 Feb 2015


Thanks for the response, while I am trying to do

I am getting below error
2619: Division by zero in an expression involving amt_allowed.

As I said in the earlier post, I have zero's in the source.

MaximeV 19 posts Joined 11/13
26 Feb 2015

filter for amt_allowed <> 0 of course ;)

Glass 225 posts Joined 04/10
26 Feb 2015

Since you cannot divide by zero you can use
or coalesce(nullifzero(amount_allowed), n) if you want a value other than null
to avoid failures.

You must sign in to leave a comment.