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..Plea se help as soon as possible..


Thanks
Priyabrata

[Michael Nemtsev [MVP]]

Re: using case statement

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


pHi Friends,
pI want to use "in" clause in case statement as shown below.
pe.g
pselect * from employee
pwhere empid in
pcase i_desgn
pwhen 'HR' then (1,2,3,4,5)
pwhen 'MD' then 1
pelse 7
pend
pThough i did this using if-elsif but there is repetation of query. So
pi want this way to implement..Plea se help as soon as possible..
p>
pThanks
pPriyabrata

[bruce barker]

Re: using case statement

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)

pintu wrote:

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..Plea se help as soon as possible..
>
>
Thanks
Priyabrata