On May 26, 2014, at 12:44 PM, Phil Longstaff <[email protected]> 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
_______________________________________________
gnucash-devel mailing list
[email protected]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
