The CASE statement in PL/SQL

Question

Hi,

I'm using Oracle 10g and I'm trying to "stack" the conditions in a CASE statement, like I would do in C++ :

case 1:
case 2:
    // instructions
    break;

i.e. having the same code block executed for two different successful conditions.

I've tried :

WHEN 1, 2 THEN
WHEN 1 OR 2 THEN

... without luck. Is it even possible ?

Thanks.

Answer 1

Did you try

WHEN (1 OR 2) THEN

?

It's CASE WHEN <expression> THEN, where <expression> is any valid expression that can evaluate to bool, so parenthesizing it should work.

Answer 2

Thanks for your input Vinko. Maybe I should have posted a full snippet :

  CASE v_n
    WHEN (1 OR 2) THEN
      dbms_output.put_line('Case 1 or 2');
    WHEN 3 THEN
      dbms_output.put_line('Case 3'); 
  END CASE;

In that case I've got an "expression is of wrong type" error. I think Oracle is trying to compute (1 OR 2) and then compare the result to v_n

Answer 3

You need to use this format:

CASE
  WHEN v_n = 1 OR v_n = 2 THEN
    dbms_output.put_line('Case 1 or 2');
  WHEN v_n = 3 THEN
    dbms_output.put_line('Case 3'); 
END CASE;

Answer 4

For a complete explanation about CASE in Oracle I suggest you read the article I wrote about this subject: What you Ought to Know About CASE in Oracle PL/SQL.