By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

need header values

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

<hi****@gmail.com> wrote in message
news:11**********************@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
Nov 12 '05 #2

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

Thanks Rhino.

Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

P: n/a
Thanks Knut, that is a cool way of doing it..

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.