overflow occured computing an expression involving

Database

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

4 REPLIES
Teradata Employee

Re: overflow occured computing an expression involving

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

Re: overflow occured computing an expression involving

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.

Teradata Employee

Re: overflow occured computing an expression involving

filter for amt_allowed <> 0 of course ;)

Enthusiast

Re: overflow occured computing an expression involving

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