Re: UNION and rows improperly unified: query optimization question

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