If statement within Where clause

Question

I am working with a query which contains "IF" statements within a "WHERE" clause. But PL\SQL Developer is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:

SELECT t.first_name,
       t.last_name,
       t.employid,
       t.status
  FROM employeetable t
 WHERE IF status_flag = STATUS_ACTIVE then t.status = 'A'
       IF status_flag = STATUS_INACTIVE then t.status = 'T'
       IF source_flag = SOURCE_FUNCTION then t.business_unit = 'production'
       IF source_flag = SOURCE_USER then t.business_unit = 'users'
   AND t.first_name LIKE firstname
   AND t.last_name LIKE lastname
   AND t.employid LIKE employeeid;

I receive the error "ORA-00920: invalid relational operator".

Placing brackets around status_flag = STATUS_ACTIVE results in error "ORA-00907: missing right parenthesis"

DCookie · Accepted Answer · 2013-03-14T04:05:48.487

18

CASE might help you out:

SELECT t.first_name,
       t.last_name,
       t.employid,
       t.status
  FROM employeetable t
 WHERE t.status = (CASE WHEN status_flag = STATUS_ACTIVE THEN 'A'
                        WHEN status_flag = STATUS_INACTIVE THEN 'T'
                        ELSE null END)
   AND t.business_unit = (CASE WHEN source_flag = SOURCE_FUNCTION THEN 'production'
                               WHEN source_flag = SOURCE_USER THEN 'users'
                               ELSE null END)
   AND t.first_name LIKE firstname
   AND t.last_name LIKE lastname
   AND t.employid LIKE employeeid;

The CASE statement evaluates multiple conditions to produce a single value. So, in the first usage, I check the value of status_flag, returning 'A', 'T' or null depending on what it's value is, and compare that to t.status. I do the same for the business_unit column with a second CASE statement.

edited Mar 14 '13 at 04:05

answered Mar 14 '13 at 00:26

DCookie

42,630
11
83
92

@DCookie, How can I add a WHERE clause condition inside an IF statement? – masT Jun 19 '14 at 12:12
Case: `t.status = null` wouldn't work for cases where t.status is actually null. Null is weird. – defactodeity Dec 18 '14 at 06:57
@defactodeity to check for null -- t.status is null – Yordan Georgiev Oct 19 '15 at 08:47
It doesn't work for me. When I try to do this I get an error (ORA-00920). The error is triggered on the `THEN` keyword – Elie G. Aug 12 '19 at 17:17

John Kugelman · Answer 2 · 2018-04-09T13:02:26.533

You can't use IF like that. You can do what you want with AND and OR:

SELECT t.first_name,
       t.last_name,
       t.employid,
       t.status
  FROM employeetable t
 WHERE ((status_flag = STATUS_ACTIVE   AND t.status = 'A')
     OR (status_flag = STATUS_INACTIVE AND t.status = 'T')
     OR (source_flag = SOURCE_FUNCTION AND t.business_unit = 'production')
     OR (source_flag = SOURCE_USER     AND t.business_unit = 'users'))
   AND t.first_name LIKE firstname
   AND t.last_name  LIKE lastname
   AND t.employid   LIKE employeeid;

If statement within Where clause

2 Answers2

Linked