Geek Answers Handbook

Case in Oracle WHERE

The following oracle query runs 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 a second one when (2 <1) two names ("Owner Role" and "Eval Owner Role"). Please indicate how the above request will change.

Thanks in advance.

-Justin Samuel

+5
source share
1 answer

Why use CASE? Why not just

AND (   ( (1 < 2) and rolename IN ('Owner Role', 'Eval Owner Role') )
     OR ( (2 < 1) and rolename IN ('Eval Owner Role') ) )

I assume that in fact you do not have predicates that are hardcoded to evaluate to TRUE (1 <2) or FALSE (2 <1) and that they actually bind the variables in your actual code.

If you really want to use the operator CASE, you can encode

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

, .

+10

More articles:


All Articles