You're right, it doesn't make sens to multiply dollars, but if you're
in a context where you don't have significant understanding of the
user's query, how do you determine if 1.05 is $1.05 or 105%?
I understand that one can custom-code everything for SQLite and get
reasonable results in some cases, but please understand that I'm
looking for solutions that don't require the framework to understand
the user's intentions any more than "I want to work with base-10
numbers up to a certain precision/scale."
Patrick Earl
On Sat, Mar 26, 2011 at 8:43 PM, Gerry Snyder <[email protected]> wrote:
> Do money values really get multiplied together?
>
> What is the meaning of square cents as a unit?
>
> Gerry
>
> On 3/26/11, Patrick Earl <[email protected]> wrote:
>> That is true, but then when you are formulating generic queries within
>> a place such as an ORM like NHibernate, you would need to figure out
>> when to translate the user's "100" into "10000". As well, if you
>> multiplied numbers, you'd need to re-scale the result. For example,
>> (1 * 1) would be (100 * 100 = 10000), which is 1 * 1 = 100. :( If one
>> wanted to get excessively complicated, they could implement a series
>> of user functions that perform decimal operations using strings and
>> then reformulate queries to replace + with decimal_add(x,y). That
>> said, it'd be so much nicer if there was just native support for
>> base-10 numbers. :)
>>
>> Patrick Earl
>>
>> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare <[email protected]>
>> wrote:
>>> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
>>>
>>>> Base-10 numbers are frequently used in financial calculations because
>>>> of their exact nature. SQLite forces us to store decimal numbers as
>>>> text to ensure precision is not lost. Unfortunately, this prevents
>>>> even simple operations such as retrieving all rows where an employee's
>>>> salary is greater than '100' (coded as a string since decimal types
>>>> are stored as strings).
>>>
>>> Can you store all money amounts as integers, as the cents value? That is
>>> exact, searchable etc.
>>>
>>> Thanks,
>>> Tom
>>> BareFeetWare
>>>
>>> --
>>> iPhone/iPad/iPod and Mac software development, specialising in databases
>>> [email protected]
>>> --
>>> Comparison of SQLite GUI tools:
>>> http://www.barefeetware.com/sqlite/compare/?ml
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [email protected]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> Sent from my mobile device
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
