using case statement

[pintu]

Hi Friends,
I want to use "in" clause in case statement as shown below.
e.g
select * from employee
where empid in
case i_desgn
when 'HR' then (1,2,3,4,5)
when 'MD' then 1
else 7
end

Though i did this using if-elsif but there is repetation of query. So
i want this way to implement..Please help as soon as possible..


Thanks
Priyabrata

 

[Michael Nemtsev [MVP]]

Hello pintu,

u can't use CASE out of SELECT statement. u need to rewrite your query

---
WBR,
Michael Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo


p> Hi Friends,
p> I want to use "in" clause in case statement as shown below.
p> e.g
p> select * from employee
p> where empid in
p> case i_desgn
p> when 'HR' then (1,2,3,4,5)
p> when 'MD' then 1
p> else 7
p> end
p> Though i did this using if-elsif but there is repetation of query. So
p> i want this way to implement..Please help as soon as possible..
p>
p> Thanks
p> Priyabrata

 

[bruce barker]

you were close. in sql case is an expression and needs to be used as a
compare in a where clause:

select * from employee
where case
when i_desgn = 'HR' and empid in (1,2,3,4,5) then 1
when i_desgn = 'MD' and empid in (1) then 1
when empid in (7) then 1
else 0
end = 1

-- bruce (sqlwork.com)