Formatting of floating point data in a DBGRID.

Posted on 2007-10-17
Last Modified: 2013-11-23
Formatting of floating point data in a DBGRID.

We have an application where a lot of price data is stored. Some of these prices may be really small with up to 15 decimals, e.g. 0,000000000000015 (volums are huge), or the may be big with few decimals.

When I try represent this in the grid I use the following formatting string:
(field as TFloatField).DisplayFormat := '#,###,##0.###############';
This works fine when the number have 15 digits store, but for other numbers this formatting string causes problems. I understand from searching the web (example:$ that this is a round of problem.

Using the formatting string above will cause 470,4 to be output as: 470,400000000000064

I am using DOA ( to access an Oracle DB, and TDBGrid to display data.

I have tried the solution outlined in doa article with setting OracleDataSet1.OracleDictionary.DisplayFormats:=true;
and manipulating OracleSession1.Preferences.FloatPrecision

The problem with this solution is that
is displayed as : 3,4E-6

Note! Apparently the formatting will work with up to 11 digits after the decimal point, but not more.

So how can I get the best of both worlds :-) Anyone knows?
Question by:Elisas
    LVL 9

    Accepted Solution

    if there is some problem with getting numbers from oracle db to delphi number program variable, try to convert number to character on database using to_char function ang get string format to delphi string program variable

    select to_char(number_column,'FM9G999G990D999999999999999') from ...
    LVL 13

    Expert Comment

    I don't know, but one idea is to store as a string. Sure you want to *work* with numbers, so just make sure that when you use the value you convert it to a number, and when you store it you convert it to a string.
    LVL 47

    Expert Comment

    When you do calculation with floating point numbers you get the
    result as approximation. This explains the case
    470,4 to be output as: 470,400000000000064

    In pure Oracle you can use ROUND(a,b) and TRUNC(a,b) numeric functions.

    Every GUI normally has a formatting section. You have to find the formatting
    section for Numbers and set the default format.

    Author Comment

    Thanks everyone.

    We have a database with thousands of tables and 60 customers with several GB if not TB of data so storing all float numbers as string is not an option.

    As I wrote I understand that the reason is a rounding problem (as explained in the links I supplied). The problem is that I can not format more than 11 decimals... I would like 15...

    I figure the solution by konektor must be the best one - even though I wish there was an easier way to get around it. You get the points konektor :-)


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now