Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post Unanswered: Formatting the decimal string

    The following query give you the easiest way to format the decimal string:

    Code:
    with input (number) as
    (select 1123167890.4327 from sysibm.sysdummy1
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(number), varchar('', 150), number - int(number), 12 
      from input 
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then '' 
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                         substr(digits(int(rem / power(10, k - 3))), 8, 3) 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where k - 3 >= 0
    )
    select num "Input.................", trans "Result"from transform_1 tr where k = 0
    So we'll get result:

    Input................. Result
    1123167890.4327 1,123,167,890.4327
    Lenny
    Last edited by Lenny77; 09-09-09 at 12:05.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Needs to be an UDF.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by Marcus_A
    Needs to be an UDF.
    I am 100% agree with you.

    Everybody can make UDF using this query.

    It' s very usable in reports. Users appreciate it.

    Lenny

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation modified formula

    I have modified the previous formula because I did not like the results where
    number < 1 (0.5 or .5 formatted as .5 which is also not bad, but I prefer to
    have the result 0.5

    Now query looks like:

    Code:
    with input (number) as
    (select -123456789.012345 from sysibm.sysdummy1
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(abs(number)), varchar('', 150), 
            abs(number) - int(abs(number)), 12 
      from input where abs(number) >= 1000   
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                     case when number < 0 then '-' else '' end 
                  || strip(digits(int(rem / power(10, k - 3))), l, '0') 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where k - 3 >= 0 and abs(number) >= 1000  
    union all
    select number, int(0), int(0), varchar(number), number, 0  
    from input
    where abs(number) < 1000  
    )
    select num "Input.................", 
             trans "Result"
      from transform_1 tr where k = 0
    Lenny
    Last edited by Lenny77; 09-09-09 at 15:47.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    With this complete formula-SQL we'll get result (for number = -.012345) :

    Input....... Result
    -.012345 -0.012345

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two issues.
    1) SQL0347W.
    2) It's not work well for negative input.

    Code:
    ------------------------------ Commands Entered ------------------------------
    with input (number) as
    (select -1123167890.4327 from sysibm.sysdummy1
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(number), varchar('', 150), number - int(number), 12 
      from input where number > 1
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then '' 
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                         substr(digits(int(rem / power(10, k - 3))), 8, 3) 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where k - 3 >= 0 and number > 1
    union all
    select number, int(0), int(0), varchar(number), number, 0  
    from input
    where number < 1  
    )
    select num, trans from transform_1 tr where k = 0;
    ------------------------------------------------------------------------------
    
    NUM              TRANS                                                                                                                                                 
    ---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL0347W  The recursive common table expression "DB2ADMIN.TRANSFORM_1" may 
    contain an infinite loop.  SQLSTATE=01605
    
    -1123167890.4327 -1123167890.4327                                                                                                                                      
    
      1 record(s) selected with 1 warning messages printed.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma
    Two issues.
    1) SQL0347W.
    2) It's not work well for negative input.

    Code:
    ------------------------------ Commands Entered ------------------------------
    with input (number) as
    (select -1123167890.4327 from sysibm.sysdummy1
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(number), varchar('', 150), number - int(number), 12 
      from input where number > 1
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then '' 
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                         substr(digits(int(rem / power(10, k - 3))), 8, 3) 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where k - 3 >= 0 and number > 1
    union all
    select number, int(0), int(0), varchar(number), number, 0  
    from input
    where number < 1  
    )
    select num, trans from transform_1 tr where k = 0;
    ------------------------------------------------------------------------------
    
    NUM              TRANS                                                                                                                                                 
    ---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL0347W  The recursive common table expression "DB2ADMIN.TRANSFORM_1" may 
    contain an infinite loop.  SQLSTATE=01605
    
    -1123167890.4327 -1123167890.4327                                                                                                                                      
    
      1 record(s) selected with 1 warning messages printed.
    I am sorry tonkuma !

    I just corrected the formula. Please use the last one.

    Let me know on issue.

    Lenny

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Corrected Formula

    This formula working good for all known to me cases:

    Code:
    with input (number) as
    (select -123456789.012345 from sysibm.sysdummy1
    ) 
    ,
    transform_1 (num, part, rem, trans, fract, k ) as
    (select number, int(0), int(abs(number)), varchar('', 150), 
            abs(number) - int(abs(number)), 12 
      from input where abs(number) >= 1000   
    union all
    select 
    number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)), 
    strip(case when  int(rem / power(10, k - 3)) = 0 and trans <= ' ' then  ''
               when  int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
                     case when number < 0 then '-' else '' end 
                  || strip(digits(int(rem / power(10, k - 3))), l, '0') 
                else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
           end 
           || case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
    , fract, k - 3
      from transform_1, input  
    where k - 3 >= 0 and abs(number) >= 1000  
    union all
    select number, int(0), int(0), varchar(number), number, 0  
    from input
    where abs(number) < 1000  
    )
    select num "Input.................", 
             trans "Result"
      from transform_1 tr where k = 0
    Lenny

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Still SQL0347W was issued.
    (I tested on DB2 9.7 on Windows.)

    To supress the warning message, I changed "where k - 3 >= 0 ..." to "where k >= 3 ...".

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 9.7 for LUW supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ).

    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.0
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    SELECT dec_nbr
         , VARCHAR( VARCHAR_FORMAT(dec_nbr, '999,999,999,999.9999'), 30 ) formatted
      FROM (VALUES 1123167890.4327, 123167890.4327, 23167890.4327, 3167890.4327, 0.5
                 , -1123167890.4327, -123167890.4327, -23167890.4327, -3167890.4327, -0.5) s(dec_nbr);
    ------------------------------------------------------------------------------
    
    DEC_NBR          FORMATTED                     
    ---------------- ------------------------------
     1123167890.4327    1,123,167,890.4327         
      123167890.4327      123,167,890.4327         
       23167890.4327       23,167,890.4327         
        3167890.4327        3,167,890.4327         
              0.5000                0.5000         
    -1123167890.4327   -1,123,167,890.4327         
     -123167890.4327     -123,167,890.4327         
      -23167890.4327      -23,167,890.4327         
       -3167890.4327       -3,167,890.4327         
             -0.5000               -0.5000         
    
      10 record(s) selected.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Ok tonkuma !

    In my enviroment it's working with any case.

    I tried to use
    Code:
    where k >= 3
    it's working too.

    Thanks, Lenny

  12. #12
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma
    DB2 9.7 for LUW supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ).

    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.0
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    SELECT dec_nbr
         , VARCHAR( VARCHAR_FORMAT(dec_nbr, '999,999,999,999.9999'), 30 ) formatted
      FROM (VALUES 1123167890.4327, 123167890.4327, 23167890.4327, 3167890.4327, 0.5
                 , -1123167890.4327, -123167890.4327, -23167890.4327, -3167890.4327, -0.5) s(dec_nbr);
    ------------------------------------------------------------------------------
    
    DEC_NBR          FORMATTED                     
    ---------------- ------------------------------
     1123167890.4327    1,123,167,890.4327         
      123167890.4327      123,167,890.4327         
       23167890.4327       23,167,890.4327         
        3167890.4327        3,167,890.4327         
              0.5000                0.5000         
    -1123167890.4327   -1,123,167,890.4327         
     -123167890.4327     -123,167,890.4327         
      -23167890.4327      -23,167,890.4327         
       -3167890.4327       -3,167,890.4327         
             -0.5000               -0.5000         
    
      10 record(s) selected.
    Good to you !
    I am working with v.8, where I don't have this function.

    Lenny

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Even for guys which are such lucky as tonkuma and can use VARCHAR_FORMAT function it could be interesting because it shown how it works.

    Lenny

  14. #14
    Join Date
    Jul 2009
    Posts
    150
    This is a man !

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is Sample UDFs for DB2 UDB V8.
    Sample UDFs for Migration

    You may be interested in:
    FnToChrN.txt TO_CHAR
    Returns character string expression of decimal number according to specified format.

Posting Permissions

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