7.9.08

ORDER BY CASE error in SQL Server

Considering am having two tables,

create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees

create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

Now I need to fetch the data from the table in the order based on the id value.
If the ID has the value as 1 then the order should be based on the first column "ID" else, the order should be based on the second column "empName" Lets see, how to achieve the same,

select empid,empname from employees
union all
select empid,'a' as empname from employeedetails
order by
case
when empid=1 then 0
else 1
end
You will get an error,
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
In this case, we should do a work around to achieve the result, Let me try with derived tables concept,
select * from
(select empid,empname from employees
union all
select empid,'a' as empname from employeedetails )
t
order by
case
when t.empid=0 then 0
else 1
end

Now, we will get the exact output.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

No comments:

Post a Comment