How to get the header values in the sql with union.

For example,
select deptno as dep,deptname as dname,location as loc,mgrno as mgr
from dept
union
select empno,empname,'Employee','' from emp

I need output with
header:
dep dname loc mgr
1 mkt 4 34
2 sls 4 33
34 James Employee 45

Thank for your time.

<hikums@gmail.com> wrote in message
news:1116344807.003195.286310@z14g2000cwz.googlegr oups.com...
> How to get the header values in the sql with union.
>
> For example,
> select deptno as dep,deptname as dname,location as loc,mgrno as mgr
> from dept
> union
> select empno,empname,'Employee','' from emp
>
> I need output with
> header:
> dep dname loc mgr
> 1 mkt 4 34
> 2 sls 4 33
> 34 James Employee 45
>
> Thank for your time.
>

I was surprised that your query didn't work just the way it was so I tried
it and confirmed that it failed to put the desired headings at the top of
the columns in DB2 for Windows/Unix/Linux, Version 8 (Fixpack 8). (By the
way, I had to change to table names from 'emp' and 'dept' to 'employee' and
'department' so I assume you are on an older version of DB2 or on a
different operating system.)

I got the desired headings to appear by putting the 'as' expressions in
_both_ queries:
select deptno as dep,deptname as dname,location as loc,mgrno as mgr
from department
union
select empno as dep,lastname as dname,'Employee' as loc,'' as mgr from
employee

Rhino

I tried my query with putting "as " in each of the select. Just putting
it on one select does not work.

Thanks Rhino.

You should specify same renamed column names explicitly for both SELECT
list.
For example,
select deptno as dep,deptname as dname,location as loc,mgrno as mgr
from dept
union
select empno as dep,empname as dname,'Employee' as loc,'' as mgr from
emp

Tonkuma wrote:

> You should specify same renamed column names explicitly for both SELECT
> list.
> For example,
> select deptno as dep,deptname as dname,location as loc,mgrno as mgr
> from dept
> union
> select empno as dep,empname as dname,'Employee' as loc,'' as mgr from
> emp

Alternatively you could do this:

SELECT *
FROM ( SELECT deptno, deptname, location, mgrno
FROM dept
UNION
SELECT empno, empname, 'Employee', ''
FROM emp ) AS t(dep, dname, loc, mgr)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Thanks Knut, that is a cool way of doing it..