Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 20 total |
Problem with $0.00 not being $0.00 |
Thu, May 20 2010 9:45 AM | Permanent Link |
Scott Rowat | Hi Tim,
I have an issue in that even though Currency field in the table looks like it is zero it is not actually zero. I have checked it to 4 decimal places (and beyond) to make sure there is no rounding issues but can't find anything. It looks like the field should be zero but when you add a filter or query such as Amount > 0 then it still shows up in the result set. I have attached a small table that exhibits the problem. If you open the TempLoanDelinquency table in DBSYS and add a filter on the Amount field, Amount > 0, you will notice that the three records that appear to have an Amount = 0 still appear. The Amount actually came from a SUM statement from another table but I can't see any issues with that table either. However if i do a HAVING SUM(Amount) > 0 clause the record still shows up even though the SUM(Amount) looks like it is zero. I am using the latest build of DBISAM 4. If you could take a look and let me know if there is an issue and how it can be fixed that would be greatly appreciated as this is causing issue in a number of tables. Sometimes a field that looks like 0 is actually 0 but there are cases when it looks like zero and is not zero. Thanks, Scott Attachments: Data.zip |
Thu, May 20 2010 11:56 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< I have an issue in that even though Currency field in the table looks like it is zero it is not actually zero. I have checked it to 4 decimal places (and beyond) to make sure there is no rounding issues but can't find anything. It looks like the field should be zero but when you add a filter or query such as Amount > 0 then it still shows up in the result set. I have attached a small table that exhibits the problem. If you open the TempLoanDelinquency table in DBSYS and add a filter on the Amount field, Amount > 0, you will notice that the three records that appear to have an Amount = 0 still appear. The Amount actually came from a SUM statement from another table but I can't see any issues with that table either. However if i do a HAVING SUM(Amount) > 0 clause the record still shows up even though the SUM(Amount) looks like it is zero. >> This is a normal issue with floating-point values. If you want exact rounding, then you should alter the fields so that they are BCD fields instead, which use the Delphi Currency type, not the Double type, for calculations. Then 0 will always equal 0, as you expect. This is because BCD/Currency fields use integers for storing the actual numbers, and just shift around the decimal point as necessary. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, May 20 2010 12:07 PM | Permanent Link |
Scott Rowat | Hi Tim,
So the Currency data type in DBISAM is not an Exact type but rather a Floating!!??!! Most Currency data types are Exact (i.e. BCD) types, specifically for this exact reason. We have a financial system and have relied heavily on the definition of a Currency data type being an Exact type. What is the point of haviing a Currency type if it is exactly the same as a Double? What distinguished the Currency data type from a Double data type. Scott |
Thu, May 20 2010 12:42 PM | Permanent Link |
Robert Kaplan | <Scott Rowat> wrote in message news:FB8E3644-7983-4065-884B-5D4BA4178CD3@news.elevatesoft.com... > Hi Tim, > > I have an issue in that even though Currency field in the table looks like > it is zero it is not actually zero. I have checked it to 4 decimal places > (and beyond) to All your dollar fields need to be decimal if you want to avoid these problems. Robert |
Thu, May 20 2010 12:48 PM | Permanent Link |
Robert Kaplan | <Scott Rowat> wrote in message news:3902C6B4-F694-4388-82ED-E7D3D31CFFDF@news.elevatesoft.com... > Hi Tim, > > So the Currency data type in DBISAM is not an Exact type but rather a > Floating!!??!! Most Currency data types are Exact (i.e. BCD) types, > specifically for this exact reason. > > We have a financial system and have relied heavily on the definition of a > Currency data type being an Exact type. Well, you're relying on an incorrect assumption. Take a deep breath, count to 10 and start changing the field definitions > > What is the point of haviing a Currency type if it is exactly the same as > a Double? What distinguished the Currency data type from a Double data > type. > See the definition of Curreny and BCD data types in the manual. Robert |
Fri, May 21 2010 6:00 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | Scott
Take a simple rule: Always use BCD field, except when you need more than 4 decimals. It will avoid a lot of rounding problems related with float data type. And other thing. This is not related with DBISAM neither ElevateDB because it is handled by DB.PAS unit and it belongs to Delphi/Rad Studio. Eduardo |
Fri, May 21 2010 8:36 AM | Permanent Link |
Bruno Krayenbuhl | > Always use BCD field, except when you need more than 4 decimals. It will
> avoid a lot of rounding problems related with float data type. It doesnt help much because TDBCFields are converted at a point or another (See DB.PAS) to/from Currency and does some rounding of its own. It does not improve precision and eats 34 bytes in the physical table where 8 bytes and some programming does the trick. When using TCurrencyField I do my own roundings (to 1,5 cents or whtever is required) always keeping in mind that there are only 15 'safe' significant digits that means a safe range from -99'999'999'999.9999 to +99'999'999'999.9999, still sufficient to handle the cumulated deficits of some large countries... > And other thing. This is not related with DBISAM neither ElevateDB because > it is handled by DB.PAS unit and it belongs to Delphi/Rad Studio. > Totaly agree. Delphi is fantastic in most respects but that is one sector where an Accounting programmer should have been included in their developpement team. Bruno |
Fri, May 21 2010 2:48 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Bruno,
<< It doesnt help much because TDBCFields are converted at a point or another (See DB.PAS) to/from Currency and does some rounding of its own. It does not improve precision and eats 34 bytes in the physical table where 8 bytes and some programming does the trick. >> The size is an issue if you're low on disk space, but there are no precision issues that I'm aware of. The translation from the TBCD record type to Currency and back should not cause any loss of precision. As an aside, ElevateDB uses a straight-up Currency type for internal storage, so the storage is 8 bytes also. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 21 2010 2:49 PM | Permanent Link |
Scott Rowat | We do our own rounding on the final result of all financial cacluation to 2-decimal places and we do integrity checks on the currency fields as part of our maintenance and haven't noticed any issues in the past. We have just started to notice a few lately and they all seem to be related to SUM operations. However, i was always under the impression that Currency fields were stored a BCD to 4-decimal places and in fact I believe that MS SQL and other major database vendors do store Currency (or Money) fields as BCD/Integers to 4-decimal places. And considering Delphi's own Currency field is just that, I'm a bit supprised to be having this issue and to find out that the Currency field in the database is actually a floating-type and not an exact-type.
Scott |
Fri, May 21 2010 2:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< So the Currency data type in DBISAM is not an Exact type but rather a Floating!!??!! Most Currency data types are Exact (i.e. BCD) types, specifically for this exact reason. We have a financial system and have relied heavily on the definition of a Currency data type being an Exact type. What is the point of haviing a Currency type if it is exactly the same as a Double? What distinguished the Currency data type from a Double data type. >> This was all decided by Borland, and has been this way since Delphi 1, so you can save your complaints for them. TCurrencyField (ftCurrency) is a floating-point field type, which you can see if you look at the TField definition (D7 DB.pas): { TCurrencyField } TCurrencyField = class(TFloatField) public constructor Create(AOwner: TComponent); override; published { Lowercase to avoid name clash with C++ Currency type } property currency default True; end; { TFloatField } TFloatField = class(TNumericField) public constructor Create(AOwner: TComponent); override; property Value: Double read GetAsFloat write SetAsFloat; published { Lowercase to avoid name clash with C++ Currency type } property currency: Boolean read FCurrency write SetCurrency default False; property MaxValue: Double read FMaxValue write SetMaxValue; property MinValue: Double read FMinValue write SetMinValue; property Precision: Integer read FPrecision write SetPrecision default 15; end; Notice that they all use AsFloat and Double values. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |