Oracle CASE WHEN - ORA-00936: missing expression

Question

I am new to oracle and below is my SQL.

SELECT * FROM TABLE1 WHERE COLUMN1 = 'YES'
AND COLUMN2 IN (


CASE WHEN EXISTS(SELECT * FROM TABLE1 WHERE COLUMN1 = 'YES' AND COLUMN2 NOT LIKE '%NO%') 
 THEN 
   SELECT COLUMN2 FROM TABLE1 WHERE COLUMN1 = 'YES' AND COLUMN2 NOT LIKE '%YES%'
 ELSE 
   SELECT COLUMN2 FROM TABLE1 WHERE COLUMN1 = 'YES' AND COLUMN2 NOT LIKE '%YES%'  END)

it is giving ORA-00936: missing expression at then statement. What am I doing wrong?

If you are only evaluating exists you shouldn't return the entire data set. Consider using something like this: `CASE WHEN EXISTS(SELECT 'TRUE' FROM TABLE1...` — Canica, Sep 24 '19 at 19:49

score 3 · Accepted Answer · answered Sep 24 '19 at 19:43

3

The subqueries after THEN and ELSE must be enclosed inside parentheses:

SELECT * FROM TABLE1 WHERE COLUMN1 = 'YES'
AND COLUMN2 IN (
  CASE 
    WHEN EXISTS (SELECT * FROM TABLE1 WHERE COLUMN1 = 'YES' AND COLUMN2 NOT LIKE '%NO%') 
      THEN (SELECT COLUMN2 FROM TABLE1 WHERE COLUMN1 = 'YES' AND COLUMN2 NOT LIKE '%YES%')
    ELSE (SELECT COLUMN2 FROM TABLE1 WHERE COLUMN1 = 'YES' AND COLUMN2 NOT LIKE '%YES%')
  END
)

This will work only if these subqueries don't return more than 1 row.
Also, both subqueries are the same. Is this a typo?
And IN can be changed to = since CASE returns only 1 value.

answered Sep 24 '19 at 19:43

forpas

160,666
10
38
76

How can I write this if it returns more than 1 row? Can I use IF ExISTS? – Chatra Sep 24 '19 at 19:49
You can't. CASE returns only 1 value – forpas Sep 24 '19 at 19:53
Okay, Can I use EXISTS ? and returns multiple values ? – Chatra Sep 24 '19 at 19:54
EXISTS returns TRUE or FALSE. It does not return rows. – forpas Sep 24 '19 at 19:56
After checking exists, can we write then and else ? – Chatra Sep 24 '19 at 20:00
Then and else are parts of a case statement. – forpas Sep 24 '19 at 20:10
then what is the best way to do this ? – Chatra Sep 24 '19 at 20:13
This answer is about the syntax error you mention in your question. If you solved this problem and you have another requirement post another question where you explain what you want to do. – forpas Sep 24 '19 at 20:14
I posted here. Please check the link https://stackoverflow.com/questions/58087576/oracle-if-exists – Chatra Sep 24 '19 at 20:20
https://stackoverflow.com/questions/58087576/oracle-if-exists-then-else Did you see this ? – Chatra Sep 24 '19 at 20:35
I did and you did not explain what you want to do, neither posted sample data and expected results. So it is not clear what you want to do. – forpas Sep 24 '19 at 20:36

Oracle CASE WHEN - ORA-00936: missing expression

1 Answers1