Thanks, that's what I need. Hard to find this in the documentation.
The query that works for me is without STR.
SELECT *
WHERE account ~ "Credit-Card:Bla:Bla"
AND NOT (FINDFIRST('Assets:Transfer.*', other_accounts))
Using it with NOT STR throws an error about bool and str not being valid
operand types for &.
Interesting issue:
SELECT *
WHERE account ~ "Credit-Card:Bla:Bla"
AND (FINDFIRST('Assets:Transfer.*', other_accounts))
throws
TypeError: unsupported operand type(s) for &: 'bool' and 'NoneType'
while
SELECT *
WHERE account ~ "Credit-Card:Bla:Bla"
AND NOT NOT (FINDFIRST('Assets:Transfer.*', other_accounts))
does exactly what I want...
Stefano
On Mon, Aug 22, 2022 at 3:14 PM Red S <redstre...@gmail.com> wrote:
> On Monday, August 22, 2022 at 12:56:50 PM UTC-7 cantas...@gmail.com 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 beancount+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/beancount/dec68b5b-f88d-41bc-9e4d-0c68cb8c267bn%40googlegroups.com
> <https://groups.google.com/d/msgid/beancount/dec68b5b-f88d-41bc-9e4d-0c68cb8c267bn%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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 beancount+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/CAMzm8_rTviw%2ByeU2_gAX8CAYh77ZgxCE7Usm5yaLOFdqbty%2Bnw%40mail.gmail.com.
