Oracle: CASE conditional STATEMENT in the WHERE Clause is not working?

Question

I have below query with CASE statement and this is trowing "missing keyword error"

Can you please help.

 select *
   from podConfigKey_Tab PCK 
  WHERE 
   CASE WHEN (PCK.Keyid = 'TLMAPIConfigMgr.UseDB' 
              and  PCK.DEFAULTKEYIDVALUE = 'FALSE')
       THEN PCK.Keyid = 'TLMAPIConfigMgr.UseDB' 
   ELSE PCK.Keyid != 'TLMAPIConfigMgr.UseDB'
    END;

The `CASE` expression returns a value; you're missing a predicate that compares that values with something. — mustaccio, Aug 10 '15 at 19:41
You may want to show some sample data and the expected query results. — mustaccio, Aug 10 '15 at 19:49

score 0 · Answer 1 · answered Aug 10 '15 at 19:41

A case expression returns a single value, not a syntactic construct like a=b. You could, however, emulate this behavior with a series of logical operators:

SELECT *
FROM   podConfigKey_Tab PCK 
WHERE  PCK.DEFAULTKEYIDVALUE = 'FALSE' OR
       PCK.Keyid != 'TLMAPIConfigMgr.UseDB'

score 0 · Answer 2 · answered Aug 10 '15 at 19:58

your query should be something more like as mentioned below (removed the else portion to make the below query work), you need to have predicate after the WHERE clause so that you can match value that is return by the CASE statment

select * from podConfigKey_Tab PCK 
 WHERE PCK.Keyid =
 CASE WHEN (PCK.Keyid = 'TLMAPIConfigMgr.UseDB'  and  PCK.DEFAULTKEYIDVALUE = 'FALSE') THEN  'TLMAPIConfigMgr.UseDB' 
END ;

score 0 · Answer 3 · answered Aug 11 '15 at 05:04

The Oracle CASE expression (like DECODE) returns a value, but by itself it is not a predicate which can evaluate to TRUE or FALSE. You need to establish some condition such that the value returned by the CASE statement can be evaluated. For example:

with sample_data as 
      (select 'dog' pet, 'y' has_fur from dual union all
      select 'cat', 'y' from dual union all
      select 'bird', 'n' from dual)
select * 
from sample_data
where (case when has_fur = 'y' then 1 else 0 end) = 1;

SQL Fiddle Example

Oracle: CASE conditional STATEMENT in the WHERE Clause is not working?

3 Answers3