How to convert FLOAT into fixed decimal string?

Last post 05-20-2013 3:45 AM by PSi. 6 replies.
Page 1 of 1 (7 items)
Sort Posts: Previous Next
  • 05-08-2013 1:20 AM

    • PSi
    • Not Ranked
    • Joined on 01-16-2013
    • Finland
    • Posts 8

    How to convert FLOAT into fixed decimal string?

    I'm trying to join currency amounts with three letter currency labes so that FLOAT currency ammounts would have rounded fixed two decimal places to give neat appearance when aligned right, like:

    F
    ---------
    14.00 EUR
     4.00 USD
     0.80 USD
     4.00 GBP
    90.80 YEN
    ---------

    For demo I'm using the Demodata that comes with PSQL 11. I use "Student" table's two columns:

    CUMULATIVE_GPA Decimal(4)
    MAJOR CHAR(20)

    so you can verify what I'm trying to do.

    I tried to use cast and convert functions but am unable to get the results I expect:

    select
     top 5
     Cumulative_GPA A,
     cast(Cumulative_GPA AS DECIMAL(6,2)) B,
     cast(cast(Cumulative_GPA AS DECIMAL(6,2)) AS CHAR(9)) C,
     convert(cast(Cumulative_GPA AS DECIMAL(6,2)), SQL_VARCHAR) D,
     Major E,
     convert(cast(Cumulative_GPA AS DECIMAL(6,2)), SQL_VARCHAR) + ' ' + Major F
    from
     "Student"

    It yelds:

     

     A        B       C    D     E            F
     -----   ----   ---   ---   ----------   -------------
     4.000   4.00   4     4     Economics    4 Economics
     4.000   4.00   4     4     Spanish      4 Spanish
     0.800   0.80   .8    .8    Chemistry    .8 Chemistry
     4.000   4.00   4     4     Economics    4 Economics
     0.801   0.80   .80   .80   Sociology    .80 Sociology
     -----   ----   ---   ---   ----------   -------------

    I expected column F to yeld:

    F
    ---------------
    4.00 Economics
    4.00 Spanish
    0.80 Chemistry
    4.00 Economics
    0.80 Sociology
    ---------------

    Am I missing something obvious? How I can get the results I expect? Is there perhaps a bug in PSQL's convert(cast(...)) combination? Using clumn B and column E and joining the data afterward is not an acceptable solution.

    pekka

  • 05-08-2013 4:43 PM In reply to

    Re: How to convert FLOAT into fixed decimal string?

    It would not be considered a defect, because the value internally is still "4" until it is translated for display purposes.

    I would use a function to format it however you need it.  Here is an example - probably not the best way to do it, but it works:

    create function formatnum(in :numvalue char(9))
    returns char(9) as
    begin
    declare :decpos integer;
    declare :ss char(9);
    set :decpos = position('.', :numvalue);
    if :decpos = 0 then set :ss=left(' ', 6-length(:numvalue)) + rtrim(:numvalue)+ '.00'; end if;
    if :decpos = 1 then set :ss=' 0.' + substring(:numvalue, 2, 2); end if;
    if :decpos > 1 then set :ss=left(' ', 7-:decpos) + left(:numvalue, :decpos+2); end if;
    if position('.', :ss) = length(:ss)-1 then set :ss = substring(:ss, 2, length(:ss)-1) + '0'; end if;
    return :ss;
    end;

    Here are the results:

    select  top 5
     Cumulative_GPA A,
     Major E,
     formatnum(cast(Cumulative_GPA AS char(9))) + ' ' + Major F
    from "Student";

         A                      E                  F                            
    ====    ===========  =============
     4.000   Economics           4.00  Economics              
     4.000   Spanish                4.00  Spanish                
     0.800   Chemistry             0.80  Chemistry              
     4.000   Economics           4.00  Economics              
     0.801   Sociology             0.80  Sociology              

  • 05-10-2013 12:48 AM In reply to

    • PSi
    • Not Ranked
    • Joined on 01-16-2013
    • Finland
    • Posts 8

    Re: How to convert FLOAT into fixed decimal string?

    Dear Linda84,

    Thanks for fast response. I'm making queries from a third party commercial product's PSQL database through ODBC so I do not think it would be wise to even try using UDFs as I might mess with the already used, or future UDFs of the product.

     I get predictable results from various other databases:

    concat(FORMAT(rate,2), ' ', des) /* MySQL */
    cast(cast(price as decimal(10,2)) as varchar(6)) + ' ' + description /* MS-SQL */
    cast( cast(price as decimal(10,2)) as text) || ' ' || productid /* PostgreSQL */

    but not from PervasiveSQL.

    BTW: How would a value of -4.009 be shown using Your function? 

    pekka

  • 05-10-2013 7:56 AM In reply to

    Re: How to convert FLOAT into fixed decimal string?

    select formatnum(-4.009) returns "-4.00"
  • 05-10-2013 8:37 AM In reply to

    • PSi
    • Not Ranked
    • Joined on 01-16-2013
    • Finland
    • Posts 8

    Re: How to convert FLOAT into fixed decimal string?

    ..so -- it is not quite right, as numeric values should be rounded, not truncated, and conversion to string should take place after converting float to suitably rounded decimal...

     Still not happy...

    pekka

     

  • 05-10-2013 8:48 AM In reply to

    Re: How to convert FLOAT into fixed decimal string?

    My function made no attempt to round.  In fact, it takes a string parameter, and returns a string.  It doesn't do any error checking, either.  It was only meant as an example on how it could be done to get you started along the path of creating your own.
  • 05-20-2013 3:45 AM In reply to

    • PSi
    • Not Ranked
    • Joined on 01-16-2013
    • Finland
    • Posts 8

    Re: How to convert FLOAT into fixed decimal string?

     I have done a (probably working) workaround, but it is not a pretty sight:

    select
      top 10
      Cumulative_GPA A,
      cast(Cumulative_GPA AS DECIMAL(6,2)) B,
      cast(cast(Cumulative_GPA AS DECIMAL(6,2)) AS CHAR(9)) C,
      convert(cast(Cumulative_GPA AS DECIMAL(6,2)), SQL_VARCHAR) D,
      Major E,
      convert(cast(Cumulative_GPA AS DECIMAL(6,2)), SQL_VARCHAR) + ' ' + Major F,
      // --------------------------------------------------
      if (
        /* if */ (Cumulative_GPA<0 and Cumulative_GPA>-1),
        /*then*/ '-',
        /*else*/ ''
      )+
      if (
        /* if */ abs(Cumulative_GPA)<1,
        /*then*/ '0',
        /*else*/ ''
      )+
      substring(
       cast(abs(round((Cumulative_GPA * 100),0))as CHAR(9)),
       1,
         if (
        /* if */ abs(Cumulative_GPA)<1,
        /*then*/ length(cast(round((Cumulative_GPA * 100),0)as CHAR(9)))-3,
        /*else*/ length(cast(round((Cumulative_GPA * 100),0)as CHAR(9)))-2
      ))+
      '.'+
      right(rtrim(cast(round((Cumulative_GPA * 100),0)as CHAR(9) )),2)+
      ' '+
      Major G
      // --------------------------------------------------
    from
    "Student"

    BTW: I noticed that in PervasiveSQL 'cast as decimal(m,n)' is not rounding -- it is truncating. MS-SQL, PostgreSQL and MySQL "cast ... decimal (m,n) ..." does the decimal coversion through rounding. From "Pervasive PSQL Engine Reference (v9 Sp2)" I learned:

      "-- Numeric string data and true numeric data are always truncated by Pervasive PSQL. Some SQL DBMS products round this data when applicable. So if you have a numeric string or true numeric value of 123.457 and you insert it into a 6 bytes string column or precision 2 numeric column, Pervasive PSQL always inserts 123.45. Other DBMS products, by comparison, may insert a value of 123.46. --"

    So this is clearly a feature -- not a bug :)

    I wonder if any of the ANSI SQL standards takes sides on this rounding with cast matter?

    pekka

Page 1 of 1 (7 items)