Re: Query in a gnucash table
From: Joseph Hesse <joeh_at_gmail.com>
Date: Thu, 12 Oct 2017 15:28:44 -0500
Message-ID: <NsidnSSX0u3hTULEnZ2dnUU7-bnNnZ2d_at_giganews.com>
>> This question refers to the following table from the gnucash program.
>> The 3 fields of interest are marked with arrows.
>>
>> MariaDB [gnucash]> describe accounts;
>> +----------------+---------------+------+-----+---------+-------+
>> | Field | Type | Null | Key | Default | Extra |
>> +----------------+---------------+------+-----+---------+-------+
>> | guid | varchar(32) | NO | PRI | NULL | | <==
>> | name | varchar(2048) | NO | | NULL | | <==
>> | account_type | varchar(2048) | NO | | NULL | |
>> | commodity_guid | varchar(32) | YES | | NULL | |
>> | commodity_scu | int(11) | NO | | NULL | |
>> | non_std_scu | int(11) | NO | | NULL | |
>> | parent_guid | varchar(32) | YES | | NULL | | <==
>> | code | varchar(2048) | YES | | NULL | |
>> | description | varchar(2048) | YES | | NULL | |
>> | hidden | int(11) | YES | | NULL | |
>> | placeholder | int(11) | YES | | NULL | |
>> +----------------+---------------+------+-----+---------+-------+
>>
>> The table describes the accounts I have. The accounts are
>> hierarchical but the table displays only one item in the hierarchy.
>> For example, I have an account:
>>
>> expenses:auto:ford:gas
>>
>> Each of the four parts above are in separate rows. If a row has name
>> "gas" then in order to find "ford" you have to look at the parent_guid
>> in that row, locate the row where guid matches parent_guid and look at
>> that name, which should be "ford". This process is repeated until all
>> the parent items of "gas" are found.
>>
>> My questions is the following. I would like to add an additional
>> column to the table called full_name which gives all of the items in
>> the hierarchy above the value in the name column. Referring to the above
>> example, if the row contains "gas", the full_name column should be
>> "expenses:auto:ford:gas".
>>
>> Is there a query that can accomplish what I want? I'm sure I could use
>> some scripting language like PHP to accomplish what I want.
>>
>> Thank you,
>> Joe
Date: Thu, 12 Oct 2017 15:28:44 -0500
Message-ID: <NsidnSSX0u3hTULEnZ2dnUU7-bnNnZ2d_at_giganews.com>
[Quoted] On 10/12/2017 02:11 PM, Jerry Stuckle wrote:
> On 10/12/2017 2:59 PM, Joseph Hesse wrote:
>> This question refers to the following table from the gnucash program.
>> The 3 fields of interest are marked with arrows.
>>
>> MariaDB [gnucash]> describe accounts;
>> +----------------+---------------+------+-----+---------+-------+
>> | Field | Type | Null | Key | Default | Extra |
>> +----------------+---------------+------+-----+---------+-------+
>> | guid | varchar(32) | NO | PRI | NULL | | <==
>> | name | varchar(2048) | NO | | NULL | | <==
>> | account_type | varchar(2048) | NO | | NULL | |
>> | commodity_guid | varchar(32) | YES | | NULL | |
>> | commodity_scu | int(11) | NO | | NULL | |
>> | non_std_scu | int(11) | NO | | NULL | |
>> | parent_guid | varchar(32) | YES | | NULL | | <==
>> | code | varchar(2048) | YES | | NULL | |
>> | description | varchar(2048) | YES | | NULL | |
>> | hidden | int(11) | YES | | NULL | |
>> | placeholder | int(11) | YES | | NULL | |
>> +----------------+---------------+------+-----+---------+-------+
>>
>> The table describes the accounts I have. The accounts are
>> hierarchical but the table displays only one item in the hierarchy.
>> For example, I have an account:
>>
>> expenses:auto:ford:gas
>>
>> Each of the four parts above are in separate rows. If a row has name
>> "gas" then in order to find "ford" you have to look at the parent_guid
>> in that row, locate the row where guid matches parent_guid and look at
>> that name, which should be "ford". This process is repeated until all
>> the parent items of "gas" are found.
>>
>> My questions is the following. I would like to add an additional
>> column to the table called full_name which gives all of the items in
>> the hierarchy above the value in the name column. Referring to the above
>> example, if the row contains "gas", the full_name column should be
>> "expenses:auto:ford:gas".
>>
>> Is there a query that can accomplish what I want? I'm sure I could use
>> some scripting language like PHP to accomplish what I want.
>>
>> Thank you,
>> Joe
> > Probably, but your question is not clear. What would the sample data > for such a table look like? >
[Quoted] I have included below 3 columns of the accounts table (not all the rows) and a fourth column which is what I would like the added column (full_name) to look like. I'm sorry about the wrap-around, I hope you can make sense of it.
+----------------------------------+-----------------------------+----------------------------------+ | guid | name | parent_guid | full_name +----------------------------------+-----------------------------+----------------------------------+ | 19c2bbc79901fe40642a414c7ba27ec6 | Gas | 478876c349c9bf5c96276971865905a7 | Expenses:Auto:Gas | 1fbc93a4cfe2fbe2ec82b0bad82deacc | Tolls&Parking | 478876c349c9bf5c96276971865905a7 | Expenses:Auto:Tolls&Parking | 5498d6474012a012447984dc04e19b7e | Service | 478876c349c9bf5c96276971865905a7 | Expenses:Auto:Service | 666c2e2bb5e1cfcbdde5b2da3d9ecab3 | AAA | 478876c349c9bf5c96276971865905a7 | Expenses:Auto:AA | 811fb79b8316c97df26f91abebbbf7df | Rental | 478876c349c9bf5c96276971865905a7 | Expenses:Auto:Rental | de4e7f441ebcfaa2ef9aaffb080d96e4 | License | 478876c349c9bf5c96276971865905a7 | Expenses:Auto:License | 478876c349c9bf5c96276971865905a7 | Auto | 5ef94a84aaaefff32eac86324671160a | Expenses:Auto | 5ef94a84aaaefff32eac86324671160a | Expenses | 14dc71d7dac3f7630f15158ab3792903 | Expenses | 9814ec9405415b3cfe67c21ad4ec780e | Software | 9a29bd3b7d185fce88ebc4119ea7d81b | Expenses:Computer:Software | d053668fcc9494b0a619d30531fdb33d | Hardware | 9a29bd3b7d185fce88ebc4119ea7d81b | Expenses:Computer:Hardware | 9a29bd3b7d185fce88ebc4119ea7d81b | Computer |5ef94a84aaaefff32eac86324671160a | Expenses:Computer ...
|----------------------------------|-----------------------------|----------------------------------|Received on Thu Oct 12 2017 - 22:28:44 CEST