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