Search code examples
oraclecasewhere-clauseclause

Oracle Case WHERE Clause

I am needing to have a CASE statement in my UPDATE statement WHERE clause. But, it is giving me a compile error:

   UPDATE......
   .
   .
   WHERE CASE
           WHEN p_client_id = 0 THEN user_id = p_user_id
           ELSE client_id = p_client_id
         END;

680/50   PL/SQL: ORA-00905: missing keyword

Am I missing something, or is this not the correct syntax?

Solution

  • I suggest you re-write it like this:

       UPDATE......
   .
   .
   WHERE (    p_client_id  = 0 and user_id   = p_user_id
           OR p_client_id != 0 and client_id = p_client_id )
         ;

    My assumption (based on the original question) is that p_client_id is always populated. If that's not the case then you will need to amend this code to handle nulls.