Reputation: 1083
Following oracle query complies and works fine:
SELECT
Employee.EmployeeId,
Employee.EmployeeName,
Employee.Description,
Employee.IsFrozen
FROM
employee, employeerole, roledef
WHERE
employee.employeeid = employeerole.employeeid
AND employeerole.roleid = roledef.roleid
AND rolename IN (
CASE
WHEN (1 < 2) THEN ('Owner Role')
WHEN (2 < 1) THEN ('Eval Owner Role')
END);
Now in my case I would like to add in second when ie (2 < 1) two rolename('Owner Role' and 'Eval Owner Role'). Kindly suggest how will the above query change.
Thanks in advance.
-Justin Samuel
Upvotes: 6
Views: 65403
Reputation: 231881
Why use a CASE
? Why not simply
AND ( ( (1 < 2) and rolename IN ('Owner Role', 'Eval Owner Role') )
OR ( (2 < 1) and rolename IN ('Eval Owner Role') ) )
I am assuming that you don't actually have predicates that are hard-coded to evaluate to TRUE (1 < 2) or FALSE (2 < 1) and that those are actually bind variables in your actual code.
If you really want to use a CASE
statement, you could code
AND( CASE WHEN (1 < 2) and rolename IN ('Owner Role', 'Eval Owner Role')
THEN 1
WHEN (2 < 1) and rolename IN ('Eval Owner Role')
THEN 1
ELSE 0
END) = 1
but that is going to be much more difficult for the optimizer to deal with and much less clear for the developer that has to maintain it.
Upvotes: 14