Re: Chart of Accounts

Sorry for the silence. Have been away on official duty. Please see inline:

--- On Mon, 13/10/08, justin <justin(at)emproshunts(dot)com> wrote:
> <SNIP>
>
> 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)

A single transaction will often have at least two entities - typically a debit and a credit. Shouldn't the two (or however may transactions there are) have the same Transaction ID? This would then lead to essentially having to split trasactions into two tables. One for the general header information, and another for the line details.

Ideas on this?

> special note do not use only 2 decimal points in the
> accounting tables.
<SNIP>

> Example would by
> Select Sum(debits) +
> Case when coa.doIhaveChildren then
> GetChildAccountDebits(coa.coa_id,
> period_id)
> else
> 0.0
> end;
> from general_ledger_transactions, coa,
> where general_ledger_transactions.coad_id = coa.coa_id
> and coa.coa_id = SomPassedAccountID
> group by general_ledger_transactions.period_id
>

I tried your function verbatim, but there were so many errors, the function could not even "compile". I tinkered with it a little bit and came up with this slightly modified version which gets "compiled":

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 ;

(I'll RTFM later to figure out what VOLATILE means :-)

When I try to use the function with a simple select, it fails with the error:

ERROR: column "coa.doihavechildren" must appear
in the GROUP BY clause or be used in an aggregate function

None of the proposed solutions make sense to me. I understand the error message (aggregation blah, blah). I just figure a way to get what I want. How did you manage to get yours working?

Thanks
James