Re: SQL errors on Trial Balance

[Dieter S. <dsi...@sq...>]

There are a few more of this type of statement in IR, IS and RP.pm

The code works on PostgreSQL 7.0 Looks like 7.1 is a bit more picky and
doesn't like table identifiers. It's logical though, there can only be one
column with one name so the table identifier is redundant, BUT, .......


Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
DWS Systems Inc.     Accounting Software       Fax: 478-5281
=========== On a clear disk you can seek forever ===========

On Mon, 6 Aug 2001, Federico Sevilla III wrote:

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