Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: need help with decimal formatting

    I have 2 problems with decimal.

    1. I want to format

    original result
    123456.78 to 0123456.78
    5555555.00 to 5555555.55

    I tried RIGHT(DIGITS(123456.78,9) with result 012345678 without decimal but I want the result to look like this 0123456.78.

    2. I want 012345678 to have decimal place 0123456.78

    Please help.

    WIN 7
    using WINSQL with DB2/400
    Last edited by xanhdieu; 02-21-12 at 14:44.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to use INSERT built-in function.

    Here is an example tested on DB2 9.7.5 on Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      sample_data(original) AS (
    VALUES
      DEC(123456.78 , 9 , 2)
    , DEC(555555.00 , 9 , 2)
    
    )
    SELECT original
         , INSERT( DIGITS(original) , 8 , 0 , '.' ) wanted_result
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    ORIGINAL    WANTED_RESULT
    ----------- -------------
      123456.78 0123456.78   
      555555.00 0555555.00   
    
      2 record(s) selected.

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

    Talking Too simple....

    you can use very simple query for this:

    Code:
    Select Right(repeat('0', 7) || varchar(decimal(try, 9, 2)), 10) form_dec
    from
    (select 123456.78
    from sysibm.sysdummy1
    union all
    select 555555
    from sysibm.sysdummy1 ) t(try)
    Result:

    FORM_DEC

    0123456.78
    0555555.00
    Lenny

  4. #4
    Join Date
    Feb 2012
    Posts
    2
    thanks for the answer. I got what I wanted

Posting Permissions

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