Re: SQL errors on Trial Balance

[Federico S. I. <ji...@le...>]

On Mon, 6 Aug 2001 at 13:42, Federico Sevilla III wrote:
> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> the result columns SELECT chart.accno, description, type, SUM(CASE
> WHEN amount < 0 THEN -amount ELSE 0 END) AS debit, SUM(CASE WHEN
> amount > 0 THEN amount ELSE 0 END) AS credit FROM chart, acc_trans
> WHERE chart.accno = acc_trans.accno AND true GROUP BY chart.accno,
> description, type UNION SELECT chart.accno, description, type, 0.0 as
> debit, 0.0 as credit FROM chart, acc_trans WHERE chart.type = 'H'
> ORDER BY chart.accno

I decided to stop crying (I cry too often) and give back to the community.
Tried the above statement in psql without the ORDER BY and noted that at
least on PostgreSQL 7.1.2 which I have the chart.accno will become accno.
So the following SELECT statement will work as verified when run from
psql:

SELECT chart.accno, description, type, SUM(CASE WHEN amount < 0 THEN
-amount ELSE 0 END) AS debit, SUM(CASE WHEN amount > 0 THEN amount ELSE 0
END) AS credit FROM chart, acc_trans WHERE chart.accno = acc_trans.accno
AND true GROUP BY chart.accno, description, type UNION SELECT chart.accno,
description, type, 0.0 as debit, 0.0 as credit FROM chart, acc_trans WHERE
chart.type = 'H' ORDER BY accno;

Only the ORDER BY changed (chart.accno --> accno).

I'm digging through the Perl code to see what changes will be needed. I've
never read the SQL Ledger code so this part may take awhile. If someone
beats me to this please let me know what diffs to do. :)

In the meantime, grep is my friend. ;>

 --> Jijo

--
Federico Sevilla III  :: ji...@le...
Network Administrator :: The Leather Collection, Inc.
GnuPG Key: <http://www.leathercollection.ph/jijo.gpg>