Difference between revisions of "GnuCash SQL Examples"
MaestroGlanz (talk | contribs) |
MaestroGlanz (talk | contribs) |
||
| Line 25: | Line 25: | ||
inner join accounts a2 on s2.account_guid=a2.guid | inner join accounts a2 on s2.account_guid=a2.guid | ||
where a1.name!=a2.name; | where a1.name!=a2.name; | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | |||
| + | |||
| + | == Return a account guid by path == | ||
| + | |||
| + | This returns a guid of an account by its path. It doesn't work perfectly as there is no real order of tuples in SQL. Therefore, if a path like Aktiva>Superbank>Supercar exists and a path like Aktiva>Supercar>Superbank as well, it won't work, as it does not return only one value. (a, e, j) is the same as (e, j, a), (j, e, a) or other orders. If a name for a bank account appears more than once in the account structure, it won't have any negative effect, as only connected chains are regarded. | ||
| + | |||
| + | <syntaxhighlight lang="sql"> | ||
| + | with path as (values('a'), ('e'), ('j')), | ||
| + | path_ancestors as (select guid, parent_guid from accounts where name in (select * from path)), | ||
| + | endnodes as ( | ||
| + | select * from path_ancestors | ||
| + | where guid not in ( | ||
| + | select s.guid from path_ancestors as s where s.guid in ( | ||
| + | select p.parent_guid from path_ancestors as p) | ||
| + | ) | ||
| + | and (select count(*) from path) in (select count (*) from path_ancestors)) | ||
| + | select * from endnodes where (select count(*) from endnodes) in ((1)); | ||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 12:51, 20 March 2021
Contents
Introduction
This page is under construction.
Template
Request all transactions like shown in the account list
This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed.
select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name;
Return a account guid by path
This returns a guid of an account by its path. It doesn't work perfectly as there is no real order of tuples in SQL. Therefore, if a path like Aktiva>Superbank>Supercar exists and a path like Aktiva>Supercar>Superbank as well, it won't work, as it does not return only one value. (a, e, j) is the same as (e, j, a), (j, e, a) or other orders. If a name for a bank account appears more than once in the account structure, it won't have any negative effect, as only connected chains are regarded.
with path as (values('a'), ('e'), ('j')),
path_ancestors as (select guid, parent_guid from accounts where name in (select * from path)),
endnodes as (
select * from path_ancestors
where guid not in (
select s.guid from path_ancestors as s where s.guid in (
select p.parent_guid from path_ancestors as p)
)
and (select count(*) from path) in (select count (*) from path_ancestors))
select * from endnodes where (select count(*) from endnodes) in ((1));
Request all child accounts of a given account
This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from sql-workbench.
with recursive account_tree as (
select guid, parent_guid
from accounts
where guid = 'Start account' -- this defines the start of the recursion
union all
select child.guid,
child.parent_guid
from accounts as child
join account_tree as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from account_tree;
