Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    5

    Unanswered: convert string to decimal in DB2

    Please help to convert a string to decimal using SQL to DB2.
    input : '1234567890'
    output required: 1234567.890

    thank you
    Venkat

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though your requirements are not clear enough(I think),
    first of all please try this.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT in_string
         , DEC(in_string) / 1000.0000000000000 AS out_decimal
     FROM  (VALUES '1234567890' 
                 , '123456789012345'
                 , '12345'
                 , '1'
           ) p(in_string)
    ;
    ------------------------------------------------------------------------------
    
    IN_STRING       OUT_DECIMAL                      
    --------------- ---------------------------------
    1234567890                            1234567.890
    123456789012345                  123456789012.345
    12345                                      12.345
    1                                           0.001
    
      4 record(s) selected.

  3. #3
    Join Date
    Oct 2014
    Posts
    5
    Quote Originally Posted by tonkuma View Post
    Though your requirements are not clear enough(I think),
    first of all please try this.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT in_string
         , DEC(in_string) / 1000.0000000000000 AS out_decimal
     FROM  (VALUES '1234567890' 
                 , '123456789012345'
                 , '12345'
                 , '1'
           ) p(in_string)
    ;
    ------------------------------------------------------------------------------
    
    IN_STRING       OUT_DECIMAL                      
    --------------- ---------------------------------
    1234567890                            1234567.890
    123456789012345                  123456789012.345
    12345                                      12.345
    1                                           0.001
    
      4 record(s) selected.


    Thank you so much for your help.

    Regards,
    Venkat

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    hi, Tonkuma :
    I am confused about the Rules for result data types of db2.
    the knowledge center describes :
    If one operand is DECIMAL(w,x) and the other operand is DECIMAL(y,z)
    The data type of the result is DECIMAL(p,s) (where p = max(x,z)+max(w-x,y-z) s = max(x,z) )
    but it seems not correct in your example...

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The operation was division.
    So, the following rule should be applied.
    Division
    The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.
    in
    DB2 for Linux UNIX and Windows 10.1.0 > Database reference > SQL > Language elements > Expressions
    http://www-01.ibm.com/support/knowle...1.0%2F2-9-2-10

    The rationale(I guessed) was "to keep enough digits of ineger part for maximum expected result(to avoid overflow)".
    The maximum digits of ineger part of result of division is p - s + s'.
    So, result scale is 31 - (p - s + s') = 31-p+s-s'.

    For example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    DESCRIBE 
    SELECT in_string
         , DEC(in_string)     AS first_operand
         , 1000.0000000000000 AS second_operand
         , DEC(in_string) / 1000.0000000000000 AS out_decimal
     FROM  (VALUES '1234567890' 
                 , '123456789012345'
                 , '12345'
                 , '1'
           ) p(in_string)
    ;
    ------------------------------------------------------------------------------
    
     Column Information
    
     Number of columns: 4
    
     SQL type              Type length  Column name                     Name length
     --------------------  -----------  ------------------------------  -----------
     448   VARCHAR                  15  IN_STRING                                 9
     484   DECIMAL               15, 0  FIRST_OPERAND                            13
     484   DECIMAL               17,13  SECOND_OPERAND                           14
     484   DECIMAL               31, 3  OUT_DECIMAL                              11
    The maximum result of
    DEC(15, 0) / DEC(17,13)
    is
    999999999999999. / 0.0000000000001
    = 9999999999999990000000000000 (28 digis)

    Then
    digits for decimal of result was left only
    31 - 28 = 3
    Last edited by tonkuma; 10-27-14 at 14:55.

Posting Permissions

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