SQL Case statement throwing missing keyword error

Question

I'm trying this query:

Select * from users_t t
where
case when sysdate <= to_date('20130131', 'yyyymmdd')
then t.user_id=1254664
else t.user_id=1259753
End

Why is it giving out "ORA-00905: missing keyword" error?

Solution 1

You need a comparison operator outside the case statement:

Select * from users_t t
where
(case when sysdate <= to_date('20130131', 'yyyymmdd')
then 254664
else 1259753
End) = t.user_id

However, you can write this without the case statement:

select *
from users_t t
where ((sysdate <= to_date('20130131', 'yyyymmdd') and t.user_id = 254664) or
      ((sysdate > to_date('20130131', 'yyyymmdd') and t.user_id = 1259753)

Solution 2

Your case statement is not correct.

SELECT * 
FROM users_t t
WHERE t.user_id = CASE WHEN SYSDATE <= TO_DATE('20130131', 'yyyymmdd')
                       THEN 1254664
                       ELSE 1259753
                  END

This will accomplish your task.

Edit: Better formatting.

Solution 3

A CASE statement in SQL always returns a value. You need to equate this CASE statement to something. Read more about it here.

You should use your code as following:

Select * from users_t t
where
t.user_id = case
                 when sysdate <= to_date('20130131', 'yyyymmdd')
                 then 1254664
                 else 1259753
            End