CODEWITHSUNDEEP
sqloracle-databaseplsql
prnjn
prnjn

Reputation: 497

how to club multiple cases with OR in case statement in plsql

i have a case statement in plsql , and the thing is for more than one case i am calling same procedure , so don't want to call the same function again and again and want to club the cases together which call same procedure.

LOOP                                                                         
FETCH cu_my_cur INTO lr_my_rec;                                                 
EXIT WHEN cu_my_cur%NOTFOUND;

CASE lr_my_rec.method_name
    WHEN 'ProductName' THEN
         -- population record with product name , product version and product type
         p_required_det(pn_product_reference => pr_mi_exits.policy_reference
                       ,pv_product_name      => pr_out_rec.product_name
                       ,pn_product_version   => pr_out_rec.product_version
                       ,pv_product_type      => pr_out_rec.product_type);

    WHEN 'ProductVersion' THEN
         -- population record with product name , product version and product type
         p_required_det(pn_product_reference => pr_mi_exits.policy_reference
                       ,pv_product_name      => pr_out_rec.product_name
                       ,pn_product_version   => pr_out_rec.product_version
                       ,pv_product_type      => pr_out_rec.product_type);
    WHEN 'ProductType' THEN 
         -- population record with product name , product version and product type
         p_required_det(pn_product_reference => pr_mi_exits.policy_reference
                       ,pv_product_name      => pr_out_rec.product_name
                       ,pn_product_version   => pr_out_rec.product_version
                       ,pv_product_type      => pr_out_rec.product_type);
    ELSE              
        NULL;
END CASE;

END LOOP;

I want something like this , 

CASE lr_my_rec.method_name 


WHEN 'ProductName' OR 'ProductVersion' OR 'ProductType' THEN 
  -- population record with product name , product version and product type
         p_required_det(pn_product_reference => pr_mi_exits.policy_reference
                       ,pv_product_name      => pr_out_rec.product_name
                       ,pn_product_version   => pr_out_rec.product_version
                       ,pv_product_type      => pr_out_rec.product_type);
ELSE 
 NULL;
END CASE;

But when trying getting error .

Expression is of wrong type.

can you guys please help me to get it done. note : I have to use switch only. cant use IF ELSE things.

Upvotes: 3

Views: 19102

Answers (3)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

Just Replace your case like below 

CASE  WHEN  lr_my_rec.method_name in ('ProductName','ProductVersion','ProductType') THEN
         -- population record with product name , product version and product type
         p_required_det(pn_product_reference => pr_mi_exits.policy_reference
                       ,pv_product_name      => pr_out_rec.product_name
                       ,pn_product_version   => pr_out_rec.product_version
                       ,pv_product_type      => pr_out_rec.product_type);

      WHEN `Other condition` THEN
          ----DO for other cases
      ELSE              
        NULL;
END CASE;

EDIT:

From Documentation. You have two Syntax for CASE

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

For the first syntax, case_value is an expression. This value is compared to the when_value expression in each WHEN clause until one of them is equal

For the second syntax, each WHEN clause search_condition expression is evaluated until one is true, at which point its corresponding THEN clause statement_list executes.

Here we want second form since we are using Search Condition. So if you want to do this. You have to change the Case Structure.

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6356

declare 
  V_TEST varchar2(10) := 'a';
  V_TEST2 varchar2(10) := 'a';
begin 
  case 
    when v_test in ('a','b','c')then 
      null;
    when V_TEST2 in ('a','b','c')then 
      null;
  else
    null;
  end case;
end;

Upvotes: 4

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Made changes like below 

    CASE 
            WHEN lr_my_rec.method_name= 'ProductName' or lr_my_rec.method_name='ProductVersion'or lr_my_rec.method_name='ProductType' 
            THEN
                 -- population record with product name , product version and product type
                 p_required_det(pn_product_reference => pr_mi_exits.policy_reference
                               ,pv_product_name      => pr_out_rec.product_name
                               ,pn_product_version   => pr_out_rec.product_version
                               ,pv_product_type      => pr_out_rec.product_type);

            ELSE              
                NULL;
        END CASE;

or you can specify condition like below

case
when lr_my_rec.method_name in('ProductName','ProductVersion','ProductType')
then
procedure
else 
null
end

Upvotes: 0

Related Questions