Conditional where clause in Oracle

Question

I have database table as below

+----------+----------+------------+
| emp_code | emp_name | emp_status | 
+----------+----------+------------+
|   01     |   aaa    |     A      |
|   02     |   bbb    |     A      |
|   03     |   ccc    |     A      |
|   04     |   ddd    |     I      |
|   05     |   eee    |     I      |
|   06     |   fff    |     R      |
+----------+----------+------------+

I want to pick values based on the emp_status which will be passed dynamically

 1. If emp_status='A' it should fetch emp_codes with emp_status='A'
 2. If emp_status='I' it should fetch all emp_codes except emp_status='I'
 3. If emp_status is null then fetch all emp_codes.

How to write single sql query with all the conditions?

Answer 1

You can use conditional WHERE clause using OR and AND operator as follows:

SELECT EMP_CODE FROM YOUR_TABLE
WHERE ('&INPUT_STATUS' = 'A' AND emp_status='A')
   OR ('&INPUT_STATUS' = 'I' AND emp_status<>'I')
   OR ('&INPUT_STATUS' IS NULL)

Cheers!!