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?

what if emp_status ='R' or else ? – Barbaros Özhan Feb 17 '20 at 11:00 — Barbaros Özhan, Feb 17 '20 at 11:00
I will be passing emp_status either A or I or null. – Pradeep Feb 17 '20 at 11:41 — Pradeep, Feb 17 '20 at 11:41

Barbaros Özhan · Answer 1 · 2020-02-17T11:36:16.377

1

You can use a case..when expression like this :

SELECT *
  FROM tab
 WHERE CASE 
       WHEN :emp_status='A' THEN 'A'
       WHEN :emp_status='I' THEN emp_status 
       WHEN :emp_status is null THEN emp_status 
        END = emp_status
   AND ( emp_status != 'I' OR :emp_status is null )

where :emp_status is bind variable to be passed into the query.

Alternatively, you can also use DECODE() function

SELECT *
  FROM tab
 WHERE DECODE( :emp_status, 'A', 'A', 'I', emp_status, null , emp_status ) = emp_status
   AND ( emp_status != 'I' OR :emp_status is null )

edited Feb 17 '20 at 11:36

answered Feb 17 '20 at 10:49

Barbaros Özhan

59,113
10
31
55

Is condition #2 from question handled here? – Popeye Feb 17 '20 at 10:51
No.still you are missing condition #2 from question. OP need `emp_status` except `I` when input is `I` – Popeye Feb 17 '20 at 11:17

score 0 · Accepted Answer · answered Feb 17 '20 at 10:48

0

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!!

answered Feb 17 '20 at 10:48

Popeye

35,427
4
10
31

Conditional where clause in Oracle

2 Answers2