Works like a charm. Thank you very much Justin. On Thu, Oct 30, 2008 at 3:49 AM, justin <justin@xxxxxxxxxxxxxxx> wrote: > There was a number of code mistakes in my examples as i was just doing it > off the top of my head, just went through it and got it all working. > I had to change the function around as it was double dipping accounts > just run this and it does work. > -------------------------------------------------------------- > > Create table coa ( > coa_id serial not null, > parent_id int not null default 0, > doIhaveChildren boolean default false, > account_name text null ); > > > Create Table general_ledger_transactions( > transaction_id serial not null, > coa_id integer, > accounting_period integer, > debit numeric(20,10) , > credit numeric(20,10), > transaction_date timestamp); > > > Create table accounting_periods ( > accounting_period serial not null, > start_date date, > end_date date, > accounting_period_Open boolean); > > Insert into coa values (10, default, True, 'ParentAccount1'); > Insert into coa values (11, 10, True, 'ChildAccount1'); > Insert into coa values (12, 11, false, 'ChildAccount2'); > Insert into coa values (13, default, false, 'ChildAccount3'); > > Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31', true > ); > Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30', true > ); > > Insert into general_ledger_transactions values( default, 11, 1, 30.0, 0.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 11, 1, 20.0, 0.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 12, 1, 10.0, 0.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 12, 1, 50.0, 0.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 11, 1, 1.0, 0.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 13, 1, 0.0, > 111.0, current_timestamp); > > > Insert into general_ledger_transactions values( default, 11, 2, 0.0, 30.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 11, 2, 0.0, 20.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 12, 2, 0.0, 10.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 12, 2, 0.0, 50.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 11, 2, 0.0, 1.0, > current_timestamp); > Insert into general_ledger_transactions values( default, 13, 2, 111.0, > 0.0, current_timestamp); > > > > CREATE OR REPLACE FUNCTION GetChildAccountDebits(PassedAccountID > integer, PassedPeriodID integer) RETURNS NUMERIC AS > $FunctionCode$ > DECLARE > retval NUMERIC = 0.0 ; > begin > return (SELECT > coalesce ( (select Sum(general_ledger_transactions.debit ) from > general_ledger_transactions where general_ledger_transactions.coa_id = > coa.coa_id and general_ledger_transactions.accounting_period = > PassedPeriodID), 0 ) + > (CASE WHEN coa.doIhaveChildren THEN > GetChildAccountDebits(coa.coa_id, PassedPeriodID ) > ELSE > 0.0 > END) > FROM coa > WHERE coa.parent_id = PassedAccountID); > end; > $FunctionCode$ > LANGUAGE 'plpgsql' VOLATILE ; > > select 10, getchildaccountdebits(10,1) > union > select 11, getchildaccountdebits(11,1) > union > select 12, getchildaccountdebits(12,1); > > > -------------------------------------------------- > > WaGathoni wrote: >> >> Justin was recommending a solution to the Chart of Accounts Problem >> posted by jamhitz: >> >> MQUOTE> >> One has you chart of Accounts >> Create table coa ( >> coa_id serial not null, >> parent_id int not null default 0, >> doIhaveChildren boolean default false >> account_name text null ) >> primary key(coa_id) >> >> Create Table general_ledger_transactions( >> transaction_id serial not null >> coad_id integer, >> accounting_period integer, >> debit numeric(20,10) , >> credit numeric(20,10), >> transaction_date datestamp) >> primary key (transaction_id) >> >> ... >> >> Create table accounting_periods ( >> accounting_period serial not null, >> start_date date, >> end_date date, >> accounting_period_Open boolean) >> >> </QUOTE> >> >> Would someone please assist me. Why is the following function:... >> >> >> CREATE OR REPLACE FUNCTION GetChildAccountDebits(PassedAccountID >> integer, PassedPeriodID integer) RETURNS NUMERIC AS >> $FunctionCode$ >> DECLARE retval NUMERIC :=0.0; >> begin >> SELECT Sum(gl_transactions.debit) + >> CASE WHEN coa.doIhaveChildren THEN >> GetChildAccountDebits(coa.coa_id, PassedPeriodID ) >> ELSE >> 0.0 >> END >> INTO retval >> FROM gl_transactions, coa >> WHERE gl_transactions.coa_id= coa.coa_id >> AND coa.parent_id = PassedAccountID >> AND gl_transactions.period_id = PassedPeriodID; >> >> RETURN retval; >> end; >> $FunctionCode$ >> LANGUAGE 'plpgsql' VOLATILE ; >> >> ....failing with an error to the effect that that that >> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY >> clause.... and what is is the recommended course of action. >> >> I have limited Internet access, so forgive me when I raise the same >> question 8 days later. >> >> Thanks >> >> > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
