Case in Oracle WHERE clause

Question

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

Answer 1

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.