Received: from beamish.nsd.ca ([205.150.156.194]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1CMgHE96635 for ; Tue, 12 Feb 2002 17:42:18 -0500 (EST) (envelope-from jllachan@nsd.ca) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id RAA21844; Tue, 12 Feb 2002 17:42:02 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma021842; Tue, 12 Feb 02 17:41:36 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id RAA31750; Tue, 12 Feb 2002 17:42:11 -0500 Message-ID: <3C699C10.4BCB1362@nsd.ca> Date: Tue, 12 Feb 2002 17:49:52 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-12 i686) X-Accept-Language: en MIME-Version: 1.0 To: Henry House CC: pgsql-sql@postgresql.org Subject: Re: UNION and rows improperly unified: query optimization question References: <20020211200524.GA17170@wotan> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/138 X-Sequence-Number: 6566 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 > for information. My OpenPGP key: . > > ------------------------------------------------------------------------ > Part 1.2Type: application/pgp-signature