recovery

[Michael Vrable]

On Fri, Nov 02, 2007 at 12:43:27PM +0900, Ian Lewis wrote:
>> Umm.. I understand exactly how gnucash stores it, but you miseed my 
>> point.  The person I was responding to was suggesting that numbers 
>> get stored as decimal, and I was asking THAT person how you would 
>> suggest that we store a decimal number that represents the number 
>> 1/3.  I know very well how gnucash does it -- it stores TWO integers 
>> that represent the rational number.  But moving that over to a SQL 
>> database might be more problematic.  I'm not sure if you can do 
>> something like:
>>
>>   SELECT * from Split where Split.amount_num/Split.amount_denom = 1/3;
>
> Afaik, doing mathematical operations like this is standard SQL. I hope 
> it should work on any SQL database. It certainly works on MySQL, 
> PostgreSQL and MSSQL.

Note that you have to be careful about _what_ mathematical operations 
are done.  If the division above is integer division, then the result 
will not be what you expect...  (1/3 would evaluate to 0, to start 
with.)

You could make the divisions floating point, but then you run into 
potential issues again with imprecision.  Better might be to rewrite the 
query so that it only requires integer arithmetic:

     SELECT * from Split where Split.amount_num * 3 = Split.amount_denom * 1;

--Michael Vrable