Case Statements, Multiple values 1

[kuhlkmk]

Hello - I would like to write the following case statement using PL/SQL. Is it possible?

Case v_code
when in ('SIT','MOV','TST') then....

I can resort to
Case
when v_code in ('SIT','MOV','TST') then.... , but I thought the 1st option would be cleaner if there is a way to do it. Thanks!

 

[SantaMufasa]

Kuhlkmk,

I am not aware that Oracle even supports the first option since a CASE statement can evaluate conditions on totally different expressions. (i.e, a CASE statement is not evaluating a single data expression (such ase "v_code")...it evaluates totally independent conditional expressions.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dagon]

I used exactly this syntax recently on a project in 9.0.2.4:

case v_no_of_segs
      when 0 then
         execute immediate v_plsql_stmt_tab(0) using p_table_name, p_business_date;
      when 1 then
         execute immediate v_plsql_stmt_tab(0) || v_plsql_stmt_tab(1) using p_table_name, p_business_date;
      when 2 then
         execute immediate v_plsql_stmt_tab(0) || v_plsql_stmt_tab(1) || v_plsql_stmt_tab(2)
                           using p_table_name, p_business_date;
      when 3 then
         execute immediate v_plsql_stmt_tab(0) || v_plsql_stmt_tab(1) || v_plsql_stmt_tab(2) || v_plsql_stmt_tab(3) 
                           using p_table_name, p_business_date;
      when 4 then
         execute immediate v_plsql_stmt_tab(0) || v_plsql_stmt_tab(1) || v_plsql_stmt_tab(2) || v_plsql_stmt_tab(3) ||
                           v_plsql_stmt_tab(4) 
                           using p_table_name, p_business_date;
      when 5 then
         execute immediate v_plsql_stmt_tab(0)|| v_plsql_stmt_tab(1) || v_plsql_stmt_tab(2) || v_plsql_stmt_tab(3) || 
                           v_plsql_stmt_tab(4) || v_plsql_stmt_tab(5)
                           using p_table_name, p_business_date;
      when 6 then
         execute immediate v_plsql_stmt_tab(0)|| v_plsql_stmt_tab(1) || v_plsql_stmt_tab(2) || v_plsql_stmt_tab(3) || 
                           v_plsql_stmt_tab(4) || v_plsql_stmt_tab(5) || v_plsql_stmt_tab(6)
                           using p_table_name, p_business_date;
      else
         raise_application_error(-20500, 'Length of generated PL/SQL statement ('||length(p_plsql_statement)||') is too large');
   end case;

 

[Dagon]

Sadly, it doesn't appear as if you can use OR or IN though.

 

[SantaMufasa]

Thanks, Dagon...this will be my "learn-something-new-every-day" entry for today. Have a star.

Cheers

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.