Using Case statement in Where clause in SQL developer

Question

I am trying to write a "case" statement inside a "where clause" which has an "in" statement in the "then" part. Basically, the following code is what I want, but it's not the Oracle correct syntax. Does anybody have any idea what the correct syntax should be like?

       create or replace  PROCEDURE "Test"
       (
        Type in number
       )
       as
       begin
       select Id, Name, AccCode from myTable
       where case Type  
                 when 0 then AccCode in (130,131)
                 when 1 then AccCode in (230,231);
       end;

Answer 1

I don't think you want a case statement. You probably just want

where (accCode in (130,131) and type = 1)
   or (accCode in (230,231) and type = 0)

If you really want to use a case statement, you could say

where (case when accCode in (130,131) then 1 
            when accCode in (230,231) then 0
            else null
         end) = type

But that will not be as readable nor will it be as easy for the optimizer to find a good execution plan.