I want to righttrim a column of decimal type by two decimal places in DB2.for example:: 3444.56788 to 3444.567 Please help me

Try the truncate function:

truncate(col1,3)


truncates the column to 3 decimal positions

Although i added the truncate function like::
Insert into table1(select truncate(sum(daytime),3) from table2) I am getting the following error.Please help.



SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003

use decimal(). for example, Decimal(3444.56788, 15,3)

SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003


I am getting the above error..Please help

try decimal(..., 31,3)

Same error again.Overflow error again. Let me know if i can try anything different

You should post your table definition and your exact SQL statements here. The thing is that you can never run into an overflow if the source table is something like DECIMAL(31,5) and the target DECIMAL(31,3).

I'll echo what Stolze said. We need the table definitions.

After playing around with the functions, the decimal function is a better option than the trunc function.

I suspect that the sum function is causing your problem. The sum is probably accumulating a value that's too large for the target column. Run the select by itself to see the value that you're trying to insert and then determine if that value will fit into the target column.

My problem got resolved after i redefined the column as (31,3) instead of (8,4)


Thanks for ur ideas

Hi,
but this was not the problem!

The value 3444.567 can be saved in DEC(8,4)! I suspect table definition is not DEC(8,4).

DEC(8,4) means:
DEC(8,4) means:
Defining a column dec(31,3) is not a good idea. To many disk space wasted. Stolce just gave you a hint to try it out and not to put in production environment.

Hope this helps,
Grofaty

3444.567 is not the data that i am using.. I justed used it as an example.So, dont consider it as input data.I have a sum(cputime) in the select statement and the cputime is defined as decimal(8,4)..I believe some of the CPUTIME data value were very big. So I wanted it be 31,3 so that it will serve all the future needs

You may want to consider using BIGINT for your data. Just multiple the CPU time by 10000 (to get rid of the fractions), then you have a whole integer.

Thanks for your tips

bigint is -9223372036854775808 to +9223372036854775807, and take 8 bytes.

decimal(31,) is 1 − 10³¹ to 10³¹ − 1. (31 bytes)?

And also decimal and integer use different rule for arith operations. decimal is more complex. But in some case, we have to use it. Without knowing what the application is used, I will stay with decimal.