pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (4+ messages)  latest in thread

Message-ID: <3C699C10.4BCB1362@nsd.ca>
Permalink:  ../3C699C10.4BCB1362@nsd.ca/
Also on:    postgresql.org/message-id/3C699C10.4BCB1362@nsd.ca

 · 

reply

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