"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:1F376B09-B755-4D71-9FDC-3C268B5105DA@news.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.
>

The problem if it is a problem has always existed in Delphi. ftCurrency and
Currency data type are not the same. For DBISAM money one should use
decimal, which assures not only no loss of precision but also gets rid of
those annoying almost zero float numbers. As for the disk space, my niece's
video collection taken with her phone is much larger than most of my
customer's databases In this time of megadrives. a few bytes are not an
issue.

As to the "some programming", are you really committed to do "some
programming" every time you do an update via SQL, etc? What if your user
wants to do his own SQL on some report writer? Storing amounts as float
always leaves the door open for some pesky problem. It is also a good idea
to have amount fields default to zero, to avoid the null issues (NULL <> 0,
etc)

Robert

Hi Scott,
I think that switching to BCD alone might not solve all currency rounding
issue.  It can't be just left to Delphi or DBISam or BCD, I think we the
programmer often have to decide and specify the precision required in the
Financial app. It is not always 2, 4, sometimes it could be 6 or more.

I have not converted my currency fields to BCD. There are only just a few
cases where you alway have to be carefull when working with currency/ double
fields.

1. Currency field X Float (e.g tax rate or quantity) and result is currency
: You MUST ALWAYS Round the result to the require precision usually 2-4
decimals
2. Iterating large tables and summing currency fields : Best to round the
result to required precision as there may be inaccuracy in cummulative
summing of the currency/ double field.
3. Queries of filters or exact values (eg  your case Amount > 0) : Changed
to Round(Amount, 2) > 0 or Round(Amount, 4) > 0 . If your running an SQL and
the currency field value might be null, you have to account for that too or
you might get unexpected results.

Hope this helps.

Regards
Gregory Sebastian

Tim,

interesting technical discussion.

> As an aside, ElevateDB uses a straight-up Currency type for internal
> storage, so the storage is 8 bytes also.
Nice.

> The size is an issue if you're low on disk space, but there are no precision
> issues that I'm aware of.   
Allow me to disagree a bit on the disk size issue. The disk size is NOT an issue nowadays, TRUE.

The issue is that, in Scott's table TempLoanDelinquency, changing all (12) Currency fields to BCD fields would mean having a record going from 184 bytes to 496 byte (184+12*(34-8)), an increase of 2.5 times.

So the hard drive will cache and move useless extra bytes >> windows will cache and move useless extra bytes >> DBSRVR  will cache and move useless extra bytes >> useless extra bytes will be moved thru the VARIOUS LAYERS of the hardware network.

My test network is WIFI with a very slow router/access point, a condition I might meet at some customer sites and being soft in the amount of data transmitted makes a difference between an acceptable and a very unpleasent user experience when you display lots of live records having some lookup fields / lookup fields(lookup fields).

>The translation from the TBCD record type to
>Currency and back should not cause any loss of precision.
Except there was an issue with TBCD in Delphi version 5.0 and older. It involves a lot of useless code because in the end TBCD is still treated as a CURRENCY except for some rounding to the correct decimal (maximum 4) places BEFORE being stored in the physical record.
(cf in BDS2006, see DB.TBCDField.GetText and following methods)

In accounting software, I never felt confortable with rounding strategies I do not well understand and to this date it has paid of.

A bit further in this discussion, Gregory Sebastian makes some suggestions. I think that the point we must keep in mind is (ANSI Standard ?) that sql treats internally most non integer fields as DOUBLE and occasionally as EXTENDED.
For DELPHI programming and PROGRAMER MANAGED SQL let's propose some options that may help (would be glad to have more suggestions).

BDS2006 / Windows XP

1.- Retrieving a TCurrencyField Currency value with an expected precision (0 to 4). It should be ok with decimal values having a total of 15 digits  (-9'999'999'999'999.99 or -999'999'999'999.999 or 99'999'999'999.9999)

function NormalizedCurr(aCurrencyField:TCurrencyField;aDecPlaces:integer):Currency;
const
 CDecDigits:array[0..4] of integer=(1,10,100,1000,10000);
begin
 // Currency is an INT64 that when formated for display is processed as an Int64*10^-4
 // Go for the real (Double) thing and strip of noisy/very small part, CPU/FPU code generated by      
 // following line uses integer arithmetics
 pInt64(@Result)^:=Round(aCurrencyField.AsFloat*CDecDigits[aDecDigits])*
                  CDecDigits[4-aDecDigits];
end;

2.- A short SQL script to get a good SUM (not exact) specially when detecting a 0 final sum value :

/* Amount being a MONEY or FLOAT sql field */
select Sum(Round(Amount*100,0))/100 as AmountSum /* or if you like *10000 ... / 10000 */
into "AmountSum"
from amount;
select AmountSum /* Shows that given 1000 records with amount=.01 and 1 record with */
from AmountSum   /* amount=-10 you'll get an effective SUM(...)=0 */
where AmountSum<>0;

- not exactly equivalent to
select Round(Sum(Amount),2) as AmountSum
-> because the Double used by the SQL in the Round(Sum(Amount),2) might still end up (if many 1000's records are involved) with the noisy bits. As a general rule Double is 'exact' at a 2 decimal positions when if multipled by 2^SomePower it is an integer.   The obvious ones are .25, .50, .75 but never .01, .05, .1

3.- To see what is realy in a Currency variable while debugging use
   CurStr=FormatCurr('#,.00000',aCur);
   or in the debug window
   pInt64(@aCur)^

   will show aCur:Currrency*10000

For more information do http://en.wikipedia.org/wiki/Floating_point and have good fun.

Bruno

ps : why does it take so long (more than a minute) to Login at elevatesoft ? missing index ?

Bruno,

<< ps : why does it take so long (more than a minute) to Login at
elevatesoft ? missing index ? >>

Most likely it's your browser - there's a redirect after the login and
certain browsers have issues with it.   What browser are you using ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> Most likely it's your browser - there's a redirect after the login and
> certain browsers have issues with it.   What browser are you using ?
>
Mozilla FireFox
version 3.6.3 French on
WINDOWS XP PRO

I timed it this time 2 m 15 s

Bruno

2 minutes to return to the discussion after clicking the POST button for the above  message

<Bruno Krayenbuhl> wrote in message
news:ED7CC043-3123-4C78-8292-694F23D51FF5@news.elevatesoft.com...
>2 minutes to return to the discussion after clicking the POST button for
>the above  message
>

Uh? I'm posting this on an old XP with about 10 programs running and it
posts instantly. You have some weird issue with your computer. Try on
another machine.

Robert

Uh?

Uh hu !

Testing Testing Testing

Bruno

Testing
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » Reply
with IE 8

Bruno

Per email, this turned out to be an issue with AVG and FireFox.

--
Tim Young
Elevate Software
www.elevatesoft.com