We built a fixed point arithmetic library using text strings. The
format stored is right justified, leading space filled decimal numbers
with embedded decimal points and leading sign. The purpose of that is
not for arithmetic efficiency but so that they can be directly output
into a printed page or HTML document. The algorithms we use are
essentially from Knuth's Semi Numerical Algorithms volume.
Functions exist for the common arithmetic operations plus moves and
comparisons. Rounding is implemented using the algorithm which
minimizes skew. These functions are also added into Sqlite as custom
functions so that the decimal numbers can be used from SQL.
We define the decimal numbers using standard SQL with precision and
scale assigned in the type declaration. Sqlite's ability to store
declared types makes the integration possible.
This is not a simple fix, but it does let us produce accurate financial
reports.
Mag. Wilhelm Braun wrote:
Thanks Stanton,
could you elaborate a bit on that - I'm not sure if I get exactly what
you mean.
if you have a smallish example would be great. the help from 'Igor
Tandetnik' with cast is a good starting point - but on selections with
max or min I still get the incorrect rounded numbers back.
EXAMPLE:column txt
"0.2009"
"10.200899"
"4.0"
"300.2009"
and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test
it returns returns 300.2008999999999999
so the only solution till now seems to make a sub-query like: SELECT txt
FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test)
not sure how messy that might get in complex queries.
anyway for any suggestion I'm more than grateful
regards W.Braun
John Stanton wrote:
Our approach to that problem was to write a library of ASCII decimal
arithmetic functions, store the data as underlying type TEXT but give
them a declared type of DECIMAL(n,m) and have added functions which
understand that declared type. With that addition Sqlite becomes
useful for accounting and other such activities requiring arithmetic
accuracy.
For a simple display interface we use display format, fixed point
decimal numbers, right justified.
Mag. Wilhelm Braun wrote:
Dear all,
I use sqlite to store numerical text strings.
Why do I use text type: because of the float problem of
incorrection. example in numeric Columns: 3.2009 returns as
3.2008999999999999 which is not what I want.
Column Type=TEXT
is there a way to do comparison of text in a numerical way.
EXAMPLE rows:Column txt
"0.200899"
"1.2009"
"113.2008999"
"4.0"
"3.1"
"3.2009"
SELECT max(txt) FROM test
should return "113.2008999" and not "4.0"
ALSO:
SELECT * FROM test WHERE txt>10.0
should just return "113.2008999" and not
"113.2008999"
"4.0"
"3.1"
so my question is there a way to do that correctly?
Thanks for any helpful hints
regards W.Braun
by the way: I use pysqlite.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
