ORACLE SQL/PL case statement - using with IN LIST

Hi all,
I have writtent he following CASE Statement in the
criteria for DeptID
CASE :1 WHEN '12345'  
THEN   '555111'   END  -- This works and brings back the specified department.
I have tried many ways to add more deparment numbers but I get a an error.
I've tried
CASE :1 WHEN '12345'  
THEN IN ("555111','12345')

also I have tried:
CASE :1 WHEN '12345'  
THEN DEPTID IN ("555111','123456')
The only one that works is when I have only one department number.  Is this because CASE statements bring only bring back 1 result?
Thanks much for your help.

Is there a way for me to list more than one dept number in a Case Statement without have to create multiple Statements?
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do not use CASE statement...

WHERE ( :1 = '12345' AND  yourfield IN ("555111','12345'))
Try this one:

CASE :1 WHEN '12345'  
THEN DEPTID END IN ("555111','123456')
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.