From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Henry House <hajhouse@houseag.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: UNION and rows improperly unified: query optimization question
Date: Tue, 12 Feb 2002 17:49:52 -0500
Message-ID: <3C699C10.4BCB1362@nsd.ca> (raw)
References: <20020211200524.GA17170@wotan>
Henry,
How about:
SELECT t.postdate, t.person,
case where g.amt >= 0 then g.amt else '0.00' end as debit,
case where g.amt < 0 then '0.00' else -g.amt end as credit, t.descr,
t.num
FROM transact t, gl_entry g
ORDER BY postdate;
Henry House wrote:
[...]
> SELECT postdate, person, debit, credit, descr, num FROM (
> SELECT t.postdate, t.person, abs(g.amt) AS debit, '0.00' AS credit, t.descr, t.num, g.id AS gid
> FROM transact t, gl_entry g ' +
> WHERE t.id = g.transact_id AND g.amt >= 0
> UNION
> SELECT t.postdate, t.person, '0.00' AS debit, abs(g.amt) AS credit, t.descr, t.num, g.id AS gid
> FROM transact t, gl_entry g
> WHERE t.id = g.transact_id AND g.amt < 0
> ) AS subselect ORDER BY postdate
>
> I would like to get rid of the outer SELECT, if possible.
>
> --
> Henry House
> The attached file is a digital signature. See <http://romana.hajhouse.org/pgp;
> for information. My OpenPGP key: <http://romana.hajhouse.org/hajhouse.asc;.
>
> ------------------------------------------------------------------------
> Part 1.2Type: application/pgp-signature
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca, hajhouse@houseag.com
Subject: Re: UNION and rows improperly unified: query optimization question
In-Reply-To: <3C699C10.4BCB1362@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox