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>

[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