From: Henry House <hajhouse@houseag.com>
To: pgsql-sql@postgresql.org
Subject: UNION and rows improperly unified: query optimization question
Date: Mon, 11 Feb 2002 12:05:24 -0800
Message-ID: <20020211200524.GA17170@wotan> (raw)
Greetings. I have an accounting system in Postgres which has tables
'transact' and 'gl_entry'. Each check, deposit, etc has one entry in
transact, but there may be multiple entries in gl_entry if one check was for
multiple expenses that are tracked separately (for example, one check to AT&T
might cover both telephone and Internet service, which are two different
costing categories). Also, debit and credit are supposed to appear in
seperate columns, even though they are one number with either positive or
negative sign in the table. So I run two select and combine them
using a union statement, but this improperly combines two gl_entry lines that
do not differ in amount or transaction ID. My solution is to select also the
unique ide number from gl_entry and remove it by wrapping the
SELECT...UNION...SELECT in another SELECT. This seems awfully ugly. Is there
a better way?
Here is the query that I use now, which produces correct results:
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;.
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: hajhouse@houseag.com
Subject: Re: UNION and rows improperly unified: query optimization question
In-Reply-To: <20020211200524.GA17170@wotan>
* 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