jralls at ceridwen.us
Tue May 27 19:54:41 EDT 2014
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.
More information about the gnucash-devel