Re: union query format

> Hi I'm trying to combine 3 query's in a union query as follows:
>
> rowdate        nco    nca    nch    aht    asa    svl
>     %abandon
>
> With Query 1;
>
> 04/01/02    1000    500    500    450    23    .23    .50
> 04/02/02    2000    500    1500    450    23    .23    .50
>
> plus Query 2;
>
> wtd        3000    1000    2000    450    23    .23    .50
>
> and Query 3;
>
> mtd        3000    1000    2000    450    23    .23    .50
>
>
> I have 2 problems once the Union Query has run.
> 1.    because Query 2 & 3 are text, it's forcing Query 1 into text and the
> date order is all messed up.
> 2. I need to formate  the fileds for svl & % abandon as a percentage.

Something like:

SELECT rowdate AS sort, rowdate::text, nco, ..., asa * 100 || '%' FROM
query1  UNION
SELECT 'infinity'::timestamp, rowdate, ... FROM query2 UNION
SELECT 'infinity'::timestamp, rowdate, ... FROM query2
ORDER BY 1

This will let you sort by the first column as dates (keeping wtd and mtd at
the end).
To show a number as percentage, x * 100 appended with '%' works fine. See
to_char() if you need more control.