On 2013/12/09 15:32, Simon Slavin wrote:
First, never do this:
CREATE TABLE IF NOT EXISTS T1(F1,F2,F3,F4,SUBTOTAL);
Always define your column types. In your case you'd be using either INTEGER or
REAL.
Agreed, those Columns have TEXT affinity by default which is wholly unsuitable for numeric comparisons such as those that prompted
this thread.
Second, multiplying by 100 and defining columns as INTEGER to store money to
two places /is/ the correct solution. This means that arithmetic will
automatically be done to two places. You will have to test whether results are
rounded or truncated when you do tax calculations but better still would be to
explicitly use round() when any dividing is done.
I cannot agree with this - maybe if you are storing a simple personal budget on your home PC, but in any real-world financial
institution or corporate finance system and even the corner-shop up your street, those milli-dollars (if you will) behind the cents
can amount to substantial amounts over some transactions and/or time and should not ever be lost to rounding. (In most large
bank-systems, the accumulated transactional amounts less than 1 cent amounts to several million per month)
What I mean is - It is quite OK to represent the bottom-line profit of a multi-billion-dollar corporate to the nearest 10 million
dollars in the report - but it is NOT OK to disregard the remainder which may be +/-5 million dollars in the calculations. It is
similarly OK to represent or format the readout of a savings account to the nearest cent - but it is NOT OK to discard the remainder
through rounded calculations. Rounding should only ever occur during "presentation". For all internal calculations the full value
should be used. Round( $ 2.3749 * TAX , 2) != Round( $ 2.37 * TAX , 2) where TAX > 0.0%
Of course there is computer methodology-imposed problem with this, in that the floating-point value result of (5-2) might be
2.999999998 as might be the closest possible 64-bit float binary that we can make to 3 [1]... so rounding to *some* degree is
advised as [(5.0 - 2) = 3] may return TRUE for some systems and FALSE for others [2] depending on type used and ALU and other system
designs which should all be irrelevant to the actual calculation, but [Round(5.0 - 2, 6) = Round(3, 6)] will always return TRUE
regardless of any system design.
What you need is a rounding that prevents FP errors but still small enough to not cause any financial deficits - A common finance
system acceptable storage is 6 decimals - that is 0.000001 dollar units rounding for -Calculations-, and 2 decimal places for
-Presentations- such as on your statement. (This is by no way a generally accepted standard, just a common practice - most banks are
even more pedantic - obviously, but it suffices for most corporates).
All that said, finding an order or invoice or any other financial system entry based on comparison of its calculated sum (or
calculated ANYTHING) is a really really bad idea... is there a reason you are not using a Key of some sort? In general you should
only look-up per value or calculation if you are reporting within ranges <=, >=, BETWEEN, etc.
Cheers,
Ryan
[1] - Quoted ex-memory as an example, actual figures may differ
[2] - Luckily most modern systems will Floatify both values of the = operand so that 5.0-2 and 3 both evaluate to 2.99999998 and
thus still end up being equal but this may be evaluated differently if you use <, >, <= or >= etc. Still, we should always use a
way that produces the correct result regardless of the cleverness/deficits of the underlying system.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
