Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 20 of 20 total |
Problem with $0.00 not being $0.00 |
Fri, May 21 2010 4:08 PM | Permanent Link |
Robert Kaplan | "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 |
Tue, May 25 2010 8:41 PM | Permanent Link |
Gregory Sebastian | 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 |
Wed, May 26 2010 8:00 AM | Permanent Link |
Bruno Krayenbuhl | 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 ? |
Thu, May 27 2010 8:07 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, May 27 2010 8:42 AM | Permanent Link |
Bruno Krayenbuhl | > 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 |
Thu, May 27 2010 8:46 AM | Permanent Link |
Bruno Krayenbuhl | 2 minutes to return to the discussion after clicking the POST button for the above message
|
Thu, May 27 2010 9:45 AM | Permanent Link |
Robert Kaplan | <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 |
Fri, May 28 2010 5:10 AM | Permanent Link |
Bruno Krayenbuhl | Uh?
Uh hu ! Testing Testing Testing Bruno |
Mon, May 31 2010 5:21 AM | Permanent Link |
Bruno Krayenbuhl | Testing
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » Reply with IE 8 Bruno |
Tue, Jun 1 2010 3:04 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Per email, this turned out to be an issue with AVG and FireFox.
-- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
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 |