Add string in output coming from DB table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add string in output coming from DB table.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2010-07-14 : 05:39:21

Dear All,

I am having following queary and output this is coming from DB table.

Queary: Select EmpName From EmployeeMaster;

Output: Nilesh
Sachin
Harish
Atul
Pritesh

Now i want to add 'All Employee' in this output, that is not in table, that is a string like,

Output: All Employee
Nilesh
Sachin
Harish
Atul
Pritesh

So can anybody tell me how to add this one in output.

Thanks and Regard's
Harish Patil

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-14 : 05:41:22

select empname from
(select 1 as id, 'All Employee' as EmpName
union
Select 2 as id,EmpName From EmployeeMaster)s
order by id

hspatil31
Posting Yak Master

182 Posts

Posted - 2010-07-14 : 06:06:11

Dear bklr,

And if i want to show multiple value suppose i have added one more column EmpID like,

select empname,EmpID from
(select 1 as id, 'All Employee' as EmpName
union
Select 2 as id,EmpName,EmpID From EmployeeMaster)s
order by id

Means suppose i want to show one more column EmpID also it giving error,

'All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.'

quote:
Originally posted by bklr

select empname from
(select 1 as id, 'All Employee' as EmpName
union
Select 2 as id,EmpName From EmployeeMaster)s
order by id

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-14 : 06:13:46

Try this,

select 0 as EmpID,'All Employee' as EmpName
union
Select EmpID,EmpName From EmployeeMaster
order by 1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2010-07-14 : 06:28:28

Dear Sir,

Thanks for the reply. It's working.
Now i want suppose in same queary department also want to show.
Like

Output:
EmpName EmpID EmpDept
All Employee 0 All Department
Nilesh 1 BB
Sachin 2 CC
Harish 3 DD
Atul 4 EE
Pritesh 5 FF

I tried but it is giving again error,
Tried queary,

select 0 as EmpID,'All Employee' as EmpName
union
select 0 as EmpID,'All Department' as EmpDept
union
Select EmpID,EmpName,EmpDept From EmployeeMaster
order by 1

quote:
Originally posted by sql-programmers

Try this,

select 0 as EmpID,'All Employee' as EmpName
union
Select EmpID,EmpName From EmployeeMaster
order by 1

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-14 : 06:32:41

select 0 as EmpID,'All Employee' as EmpName, 'All Department' as EmpDept
union
Select EmpID,EmpName,EmpDept From EmployeeMaster
order by 1

when u r using union the both select statements should have same number of the column with same datatypes.......

hspatil31
Posting Yak Master

182 Posts

Posted - 2010-07-14 : 07:01:24

Dear bklr,

Thanks very much for reply. I am trying same example in Oracle10g with sqldeveloper but this is not working.
So can you plz tell me y it is not working ?

quote:
Originally posted by bklr

select 0 as EmpID,'All Employee' as EmpName, 'All Department' as EmpDept
union
Select EmpID,EmpName,EmpDept From EmployeeMaster
order by 1

when u r using union the both select statements should have same number of the column with same datatypes.......

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-14 : 07:08:18

try this
select 0 as EmpID,'All Employee' as EmpName, 'All Department' as EmpDept from dual
union
Select EmpID,EmpName,EmpDept From EmployeeMaster
order by 1

hspatil31
Posting Yak Master

182 Posts

Posted - 2010-07-14 : 07:27:56

Dear bklr,

Thanks very much. My problem is solved. Is there any method to give point or automaticaly u will get.

quote:
Originally posted by bklr

try this
select 0 as EmpID,'All Employee' as EmpName, 'All Department' as EmpDept from dual
union
Select EmpID,EmpName,EmpDept From EmployeeMaster
order by 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources