On Monday, August 22, 2022 at 12:56:50 PM UTC-7 [email protected] wrote:
> My question might be OT for this thread, but it connects to the FROM/WHERE
> topic.
> How do I write a BQL query that selects transactions from certain
> accounts, and excludes those transactions that have a posting that matches
> another account? For example, select all transactions from
> Credit-Card:Bla:Bla that don't have Assets:Transfer in the postings. I
> tried FROM accounts ~ 'Credit-Card:Bla:Bla' and WHERE with Assets:Transfer,
> but I always get a syntax error. (pseudocode, not the actual query...)
>
FROM accounts ~ 'Blah' is invalid because transactions don't contain
accounts (not directly). Postings have an account. You proved my point
above with this being a common stumbling block, heh :).
As Dan advised above, use a WHERE instead of from. It helps to remember
that a query in BQL filters postings via the WHERE. So you're selecting
postings with a certain account, but you want to filter out postings where
the posting's parent transaction has another posting with a certain account.
SELECT *
WHERE account ~ "Credit-Card:Bla:Bla"
AND NOT STR(FINDFIRST('Assets:Transfer.*', other_accounts))
Try this for edification: "SELECT date,description,account,other_accounts".
'help targets' in BQL would've listed the other_accounts and FINDFIRST
functions, but IMHO, constructing the query above is very difficult to do
from the documentation. Perhaps a BQL cookbook would help.
--
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/dec68b5b-f88d-41bc-9e4d-0c68cb8c267bn%40googlegroups.com.
