Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-25-2015
02:33 PM

02-25-2015
02:33 PM

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

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-26-2015
03:19 AM

02-26-2015
03:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-26-2015
06:56 AM

02-26-2015
06:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-26-2015
08:35 AM

02-26-2015
08:35 AM

filter for amt_allowed <> 0 of course ;)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-26-2015
08:48 AM

02-26-2015
08:48 AM

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