Oracle SQL: CASE construct in WHERE clause with IN clause

In this article, let us understand how useful CASE construct in the below mentioned scenario. This would be a very common use case that would arise when we need to pass parameters and fetch records based on condition.

Use Case Scenario:

Employee table has employee records from different departments eg: Accounts, IT, Finance and we need to fetch employee details based on department name however, parameter (p_dept_name) do have the value "All" that should fetch employee records from all departments.

Solution:

SELECT e.name "Employee Name", d.name  "Department Name"

   FROM emp e, dept d

WHERE e. dep_id = d.dept_id

      AND 1 = (CASE WHEN p_dept_name = 'All' AND d.name IN ('Accounts','IT','Finance') 

                                    THEN 1

                                    WHEN p_dept_name != 'All' AND d.name = p_dept_name

                                    THEN 1

                        END);