Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2012
    Posts
    20

    Unanswered: SQL Select - Formatted output of Decimal

    Hello,

    I have a question on a formatted output of a specific Decimal(19,4) field called units.

    Example values within this column:
    1985.471
    178556.1
    99999997896.4789

    What I need to have within my SQL Select query is to show each of those numbers always with 5 decimals and no thousander separator (independent if they have 1, 2, 3 or 4) (missing decimals should be filled with 0 at the right). The figure could as well get transformed to a varchar, that does not matter here. (Similar to Access Function Format([units],'###0.00000')

    For the above listed example numbers the output should look like:
    1985.47100
    178556.10000
    99999997896.47890

    I tried to do this with Round, VARCHAR_FORMAT but none of these solved my issue

    Can anyone please let me know how this works?

    Thanks very much in advance!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    something like
    db2 "select dec(col1,9,5) from dd"

    1
    -----------
    25.00000
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2012
    Posts
    20
    thanks for the answer, but this is not working.
    One point I wrote incorrectly in my original post. the final output should be a char/varchar value, no number any more.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there are functions as char.... to convert numeric to character
    see infocenter for details
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Sep 2012
    Posts
    20
    thanks for the info. But the issue I have is to format the number correctly before I transform it into char/varchar. I do not know how I can force the output always to show 5 decimals.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to do this with Round, VARCHAR_FORMAT but none of these solved my issue
    What format-string with VARCHAR_FORMAT did you tried?

  7. #7
    Join Date
    Sep 2012
    Posts
    20
    Hi, I tried it with VARCHAR_FORMAT(units, 'S99999999999.99999'), the error message I am getting is:

    Error number -239:
    DMS-E-GENERAL, A general exception has occurred during operation 'prepare request with options'.
    DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
    [IBM][CLI Driver][DB2/NT64] SQL0440N No authorized routine named "VARCHAR_FORMAT" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

    I assume I cannot use this function with the current Database Version we have in place. Correct?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So, I think that you are using older DB2 version.
    What DB2 version/release and platform OS are you using?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curious, but the whole idea of formatting data at the SQL side seems wrong to me. I always try to format as close the client or "consumer" end as possible, usually via either CSS, a thin client app, or at worst case on an application server. I avoid doing formatting on the database if there is any other way.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Sep 2012
    Posts
    20
    Service_level
    db2/nt64 9.5.8

    fixpack_num
    8

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    CHAR( DEC(units , 20 , 5) )

  12. #12
    Join Date
    Sep 2012
    Posts
    20
    That's it nearly. Only issue I have now are the leading zeros (for negative numbers the - sign should stay).

    Original Figures:
    428.879
    -5552.107

    CHAR(DEC(units , 20 , 5)) Figures:
    000000000000428.87900
    -000000000005552.10700

    Required Figures:
    428.87900
    -5552.10700

  13. #13
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there is something like the infocenter
    IBM Knowledge Center
    where you can find all the info, if you take the time to read it..
    or if prefer the spoon-feeding method, you just have to wait for someone to reply.. Me myself I do not participate in that kind of game..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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