Group BY

[WaGathoni <is.mundu@xxxxxxxxx>]

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