Oracle Case WHERE Clause-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

One option is to use DECODE() function to match conditionally such as

WHERE DECODE(p_client_id,0,user_id,client_id)=DECODE(p_client_id,0,p_user_id,p_client_id)