pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
UNION and rows improperly unified: query optimization question
4+ messages / 4 participants
[nested] [flat]

* UNION and rows improperly unified: query optimization question
@ 2002-02-11 20:05 Henry House <hajhouse@houseag.com>
  2002-02-12 01:18 ` Re: UNION and rows improperly unified: query optimization question Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-12 22:49 ` Re: UNION and rows improperly unified: query optimization question Jean-Luc Lachance <jllachan@nsd.ca>
  2002-02-16 07:25 ` Re: UNION and rows improperly unified: query optimization question Marshall Spight <marshall@meetstheeye.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Henry House @ 2002-02-11 20:05 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: UNION and rows improperly unified: query optimization question
  2002-02-11 20:05 UNION and rows improperly unified: query optimization question Henry House <hajhouse@houseag.com>
@ 2002-02-12 01:18 ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2002-02-12 01:18 UTC (permalink / raw)
  To: Henry House <hajhouse@houseag.com>; +Cc: pgsql-sql

Henry House <hajhouse@houseag.com> writes:
> So I run two select and combine them
> using a union statement, but this improperly combines two gl_entry lines th=
> at
> do not differ in amount or transaction ID.

AFAICT you do not want duplicate-row elimination at all.  Why don't you
use UNION ALL?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: UNION and rows improperly unified: query optimization question
  2002-02-11 20:05 UNION and rows improperly unified: query optimization question Henry House <hajhouse@houseag.com>
@ 2002-02-12 22:49 ` Jean-Luc Lachance <jllachan@nsd.ca>
  2 siblings, 0 replies; 4+ messages in thread

From: Jean-Luc Lachance @ 2002-02-12 22:49 UTC (permalink / raw)
  To: Henry House <hajhouse@houseag.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: UNION and rows improperly unified: query optimization question
  2002-02-11 20:05 UNION and rows improperly unified: query optimization question Henry House <hajhouse@houseag.com>
@ 2002-02-16 07:25 ` Marshall Spight <marshall@meetstheeye.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Marshall Spight @ 2002-02-16 07:25 UTC (permalink / raw)
  To: pgsql-sql

"Henry House" <hajhouse@houseag.com> wrote in message news:20020211200524.GA17170@wotan...
> 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 th=
> at
> 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?

The result of a SELECT statement is a set of values, in the mathematical sense of
the word "set". Sets do not have duplicate values, so here the database is behaving
correctly. That is what UNION means, in SQL, and in any math textbook.

What's wrong with just the inner select? Why do you have to get rid of the
unique id? It's not hurting anything; it's helping in fact.


Marshall






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-02-16 07:25 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-11 20:05 UNION and rows improperly unified: query optimization question Henry House <hajhouse@houseag.com>
2002-02-12 01:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-12 22:49 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-02-16 07:25 ` Marshall Spight <marshall@meetstheeye.com>

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