Thread: 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.

On Fri, 26 Apr 2002 15:46:21 -0400Robert Wynter <rgwynter@rci.rogers.com> wrote:
> Hi Josh,
> Thanks for the help.
> 
> Question, How do I format in a union query. Is there a SELECT SQL
> code I
> could use. I've been trying to find internet code examples to find
> that out.

Just like you would in a regular query.  

SELECT to_char(sub_date, 'YYYY-MM-DD') as entry, adv, etc ....
UNION ALL
SELECT mps, adv, etc .....

(I generally use UNION ALL because it doesn't require the DB to test
for uniqueness and thus runs faster in cases where I expect all rows to
be unique anyway).

-Josh

> 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.