title: case, when and then on where clause in stored procedure is not working
body: <p>The following stored procedure is doing a product search based on search type(id, number, name) and search text('samsung galaxy' or any other etc..).
  Let us assume </p>
  
  <p><strong>search_type is name</strong> and
  <strong>search_text is 'samsung galaxy'</strong></p>
  
  <p>based on this conditions I've written the following stored procedure.</p>
  
  <pre><code>create or replace procedure product_search(
      search_type IN varchar2,
      search_text IN varchar2,
      status IN varchar2)
  is
  cursor c1 is 
      SELECT p.pm_oid , p.item_name  
          FROM prism_item_hierarchy p
          WHERE 
          CASE search_type
               WHEN 'id' THEN p.pm_oid LIKE '%'||search_text||'%'
               WHEN 'name' THEN  Lower(p.item_name ) LIKE '%'||search_text||'%'
               ELSE p.item_number LIKE '%'||search_text
          END;
  
  BEGIN
      open c1;
      -- fetch c1 into 
  
      close c1;
  
   END;
  </code></pre>
  
  <p>But I am getting the following error on the case-when-condition:</p>
  
  <pre><code>[Error] ORA-00905 (18: 49): PL/SQL: ORA-00905: missing keyword
  </code></pre>
  
  <p>Could you please suggest to me how to solve this problem, even if you suggest an other way to solve this problem, it would be great. Thanks</p>