Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2007
    Posts
    66

    Unanswered: right trim a decimal column

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Try the truncate function:

    truncate(col1,3)


    truncates the column to 3 decimal positions

  3. #3
    Join Date
    Jun 2007
    Posts
    66
    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

  4. #4
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by nivasreddy007
    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
    use decimal(). for example, Decimal(3444.56788, 15,3)

  5. #5
    Join Date
    Jun 2007
    Posts
    66
    SQL0413N Overflow occurred during numeric data type conversion.
    SQLSTATE=22003



    I am getting the above error..Please help

  6. #6
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by nivasreddy007
    SQL0413N Overflow occurred during numeric data type conversion.
    SQLSTATE=22003



    I am getting the above error..Please help
    try decimal(..., 31,3)

  7. #7
    Join Date
    Jun 2007
    Posts
    66
    Same error again.Overflow error again. Let me know if i can try anything different

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    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.

  10. #10
    Join Date
    Jun 2007
    Posts
    66
    My problem got resolved after i redefined the column as (31,3) instead of (8,4)


    Thanks for ur ideas

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    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:
    Code:
    3444.567
    1234 567 -> 7 is enough. So 8 is OK.
    DEC(8,4) means:
    Code:
    3444.567
         123 -> so 3 is enough. So 4 is OK
    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
    Last edited by grofaty; 08-07-07 at 03:26.

  12. #12
    Join Date
    Jun 2007
    Posts
    66
    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

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Jun 2007
    Posts
    66
    Thanks for your tips

  15. #15
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by stolze
    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.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •