CODEWITHSUNDEEP
oracle-databasecasewhere-clause
Yasen
Yasen

Reputation: 3428

Oracle - use CASE in WHERE and IN()

Is it possible to have a CASE IN() clause in WHERE of Oracle?

For example:

SELECT vip 
FROM table 
WHERE
  CASE WHEN $inputVar LIKE '' 
  THEN vip IN (1,2,3)
  ELSE vip in ($inputVar)
  END

Edit: With this code I get the error:

ORA-00905: missing keyword

Upvotes: 0

Views: 264

Answers (1)

DB_learner
DB_learner

Reputation: 1026

do it with a subquery.

SELECT vip 
FROM table 
WHERE
vip in
 (select
  CASE WHEN $inputVar LIKE '' 
  THEN 1
  ELSE $inputVar
  END from dual)

But if you want the default value as multiple records seperated by comma, It cannot be done. You have to frame it dynamically.

Do it in query with help of union

select * from table where vip in (1,2,3) and $inputvar = ''
union all
select * from table where vip in $inputvar

Upvotes: 1

Related Questions