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

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

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

<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

<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

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

> 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

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

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

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