Re: Group BY and Chart of Accounts - Mailing list pgsql-general

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
>
>