Group BY

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

On Thu, Oct 30, 2008 at 3:49 AM, justin <justin@emproshunts.com> 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