On 27/05/2022 13:11, Philippe Dumonet wrote:
Hey, another thing: how do I access inventories in the filter (WHERE)
part of my queries? I've looked at the help provided from within the
interactive query command line and there seems to be a bunch of simple
functions that take an inventory as an input but I can't seem to find
the relevant column. It keeps telling me "Invalid column name
'inventory' in WHERE clause context" and I can't access my own columns
or use sum within the filter part.
Aggregates cannot be used in the WHERE clause as the WHERE clause is
executed to filter the rows before computing the aggregates. To filter
on aggregates you would need to specify an HAVING clause, but it is not
implemented in bean-query as included in Beancount v2.
Working toward Beancount v3, some parts of Beancount have moved to
separate projects. This is the case of bean-query, which has moved to
its own repository https://github.com/beancount/beanquery/
beanquery implements the HAVING clause and the empty() BQL function that
checks whether an inventory is empty, see
https://github.com/beancount/beanquery/issues/36
beanquery can be installed with Beancount v2 and will replace the
bean-query command with the newer version. A release has not been tagget
yet, thus you would need to install from git. beanquery is evolving
rapidly (at least in comparison to the time I have to dedicate to it)
and the test coverage is not perfect yet, thus some bugs may sneak in.
Please give it a try and report any issue you find.
You can find the example ledger file I'm experimenting with here
<https://pastebin.com/LXYZ1Z05>. The query I'm running is "SELECT
account, cost_date as date, sum(position) as position,
cost(sum(position)) as cost, cost_number as num WHERE currency != 'EUR'
GROUP BY account, currency, cost_date, cost_number". I'm trying to
filter out rows which have no inventory.
I would write this like this:
SELECT
cost_date AS date,
sum(position) AS position,
cost(sum(position)) AS cost
WHERE
account = 'Assets:Crypto'
GROUP BY
currency,
cost_date
HAVING
not empty(sum(position))
ORDER BY
date
Cheers,
Dan
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/66444718-62ec-f061-f842-90bd8775d82c%40grinta.net.
