On Mon, Dec 18, 2000 at 11:53:22AM +1000, Phillip Shelton wrote:
> (Taken from David M's schema.txt)
>
> The scu is used as the denominator in splits against this account. It is
> enforced by the engine. Why do we not just use what is defined for the
> commodity?
>
> <my comment>
> Dave P has answered this one.
> </my comment>
I'll have to go look for it.
> Accounts can have subaccounts. The subaccounts are identified by the
> parent_account_guid, which indicates the parent account.
>
> <question>
> What happens for the accounts that have no parent?
>
> If an account has both children and splits, what would the SQL be if you
> wanted to look at all the splits under that account whether in the account
> directly or the child?
> </question>
SELECT S.*
FROM ACCOUNT A, SPLIT S
WHERE (A.ACCOUNT_GUID = S.ACCOUNT_GUID)
AND ((A.ACCOUNT_GUID = 'THEGUID') OR (A.PARENT_ACCOUNT_GUID = 'THEGUID'));
This handles only one level down. The following algorithm works for
multiple levels if the second statement is iterated until it yields no
additional records. It requires a temporary table.
INSERT INTO TEMP (ACCOUNT_GUID) VALUES ('TheStartingAccountGUID');
SELECT ACCOUNT_GUID
FROM ACCOUNT
INTO TEMP
WHERE ACCOUNT_GUID IN
(SELECT A.ACCOUNT_GUID
FROM TEMP T, ACCOUNT A
WHERE T.ACCOUNT_GUID = A.PARENT_ACCOUNT_GUID);
SELECT *
FROM SPLIT
WHERE ACCOUNT_GUID IN
(SELECT ACCOUNT_GUID FROM TEMP);
--
Dr. David C. Merrill http://www.lupercalia.net
Linux Documentation Project [EMAIL PROTECTED]
Collection Editor & Coordinator http://www.linuxdoc.org
Finger me for my public key
I who am the beauty of the green Earth,
And the white moon among the stars,
And the mysteries of the waters,
I call upon your soul
To arise and come unto me.
-- from The Charge of the Goddess, Doreen Valiente
_______________________________________________
gnucash-devel mailing list
[EMAIL PROTECTED]
http://www.gnumatic.com/cgi-bin/mailman/listinfo/gnucash-devel
