Rethinking Numeric

[John Ralls]

On May 26, 2014, at 12:44 PM, Phil Longstaff <phil.longstaff at gmail.com> wrote:

> Re numeric in queries...
> 
> At one point, I was playing with not loading all transactions/splits on
> startup.  However, I still needed the account balances.  I remember using a
> (pseudo-sql) query something like:
> 
>    select sum(amount.numerator) from splits where account_guid='the one I
> want' and date < today group by amount.denominator
> 
> I could then add a 'where' clause for reconciliation state and repeat to
> get reconciled balance and another state for cleared balance.  I could have
> gotten more than one value back, with different denominators, but in
> practice that never happened.  If it had, I was prepared with a small loop
> which just added the values together.
> 
> Come to think of it, I might even have removed 'account_guid="the one I
> want"' and grouped by account_guid as well as by amount.denominator so that
> I had an array of results, one per account.
> 
> This never went ahead because of the code in gnucash which assumes all
> splits are in memory, but the code might still be in there somewhere but
> never used.  I can dig further if you want.

Yup, gnc_sql_get_account_balances_slist( GncSqlBackend* be ) in gnc-transaction-sql.c, starting at line 1334.
The actual line is
    buf = g_strdup_printf( "SELECT account_guid, reconcile_state, sum(quantity_num) as quantity_num,
                            quantity_denom FROM %s GROUP BY account_guid, reconcile_state, quantity_denom ORDER BY account_guid,
                            reconcile_state”, SPLIT_TABLE );

I’d actually noticed that a couple of months ago when I was working on the private-kvp branch.

I’m surprised you never saw different denominators, since GncNumerics are supposed to reduce; e.g $0.50 should be 1/2. Even so it would be better than getting a cursor and iterating over every split in an account to get the totals, but not as good as just being able to sum the quantities.

Regards,
John Ralls