CODEWITHSUNDEEP
mysqloracle-databasepostgresqlcase
Manas Saxena
Manas Saxena

Reputation: 2365

ORA-00905: missing keyword for THEN clause of CASE?

I have the following query:

SELECT name,tid FROM table_name WHERE status=1 AND
CASE
WHEN EXISTS (SELECT 1 FROM table_name WHERE name='name1' AND tid='tid1' AND status=1)
THEN name='name1' AND tid='tid1'
ELSE name='name2' AND tid='tid2'
END;

The above query works for postgres and mysql but in oracle I get this error :

THEN name='name1' AND AND tid='tid1'
         *
ERROR at line 4:
ORA-00905: missing keyword

Can someone please tell me whats wrong here and how to correct the query to work for all three - postgres , mysql and oracle

Upvotes: 0

Views: 764

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

In Oracle you can't use a CASE expression to 'add' clauses to a WHERE clause. If I understand what you're trying to do correctly it looks to me like your query can be simplified to

SELECT name, tid
  FROM table_name
  WHERE STATUS = 1 AND 
        ((NAME = 'name1' AND
          TID = 'tid1') OR
         (NAME = 'name2' AND
          TID = 'tid2'))

Best of luck.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

In Oracle, a CASE statement cannot return a boolean expression. Here is one way to do what you want without a CASE:

SELECT name, tid
FROM table_name
WHERE status = 1 AND
      (EXISTS (SELECT 1 FROM table_name WHERE name = 'name1' AND tid = 'tid1' AND status = 1) AND
       name = 'name1' AND tid = 'tid1'
      ) OR 
      (NOT EXISTS (SELECT 1 FROM table_name WHERE name = 'name1' AND tid = 'tid1' AND status = 1) AND
       name = 'name2' AND tid = 'tid2'
      );

Or, you could express this as:

SELECT name, tid
FROM table_name
WHERE status = 1 AND
      (CASE WHEN EXISTS (SELECT 1 FROM table_name WHERE name = 'name1' AND tid = 'tid1' AND status = 1) 
            THEN (CASE WHEN name = 'name1' AND tid = 'tid1' THEN 'true' END)
            ELSE (CASE WHEN name = 'name2' AND tid = 'tid2' THEN 'true' END)
      ) = 'true'

Understanding CASE with booleans can be tricky. This is even more true with nested CASE statements. However, this version does have the advantage of only running the subquery once.

Upvotes: 1

Related Questions