Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stack conditions in CASE statement in PL/SQL

Tags:

oracle

plsql

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 ?

EDIT - 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;

Generates an expression is of wrong type error

like image 822
Xavier Poinas Avatar asked Nov 22 '08 10:11

Xavier Poinas


2 Answers

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;
like image 65
WW. Avatar answered Oct 04 '22 04:10

WW.


It is possible to use IN just as in SQL:

BEGIN
  CASE 
    WHEN EXTRACT(YEAR FROM SYSDATE) IN (2015, 2016, 2017) THEN
      dbms_output.put_line('Yes');
    ELSE
      dbms_output.put_line('No');
  END CASE;
END;
/
like image 20
Dmitriy Korobskiy Avatar answered Oct 04 '22 02:10

Dmitriy Korobskiy