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)
Datatype
actual_amt decimal(9,2)
amt_allowed decimal(9,2)
percent_col decimal(5,2)
expected is
actual_amt/amt_allowed*100-->percent_col
5679.89/4569.67*100 --> 124.29
 
I am using the below update statement.
 
update dbname.tgt_table_name
set
percent_col= 100*(cast(actual_amt/amt_allowed) as decimal(9,2))
where
cast(actual_amt as decimal(9,2) <> cast(0 as decimal(9,2))
and
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.
 
Regards,
John

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

Hi,

Thanks for the response, while I am trying to do
max(actual_amt/amt_allowed)*100

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

John,
Since you cannot divide by zero you can use
max(actual_amt/nullifzero(amt_allowed))*100 
or coalesce(nullifzero(amount_allowed), n) if you want a value other than null
to avoid failures.
Rglass

You must sign in to leave a comment.