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)
5679.89/4569.67*100 --> 124.29
I am using the below update statement.
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.
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
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.
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.