title: Check Multiple Values in WHERE Clause SQL Query REGEXP_LIKE Oracle 11g
body: <p>if using REGEXP_LIKE in complex query it take very long time to execute, Can you provide alternate solution for that.</p>
  
  <p>get List of values from one query and should use only those values to another query where clause</p>
  
  <pre><code>LV_TRANS_TYPES_IDS  VARCHAR2(4000);
  
  
    SELECT LISTAGG(TRANSACTION_TYPE_ID, '|') WITHIN GROUP(ORDER BY 1)
      INTO LV_TRANS_TYPES_IDS
      FROM MTL_TRANSACTION_TYPES
     WHERE TRANSACTION_TYPE_NAME IN
           (SELECT MEANING
              FROM FND_LOOKUP_VALUES_VL
             WHERE LOOKUP_TYPE = 'LOOKUPNAME');
  </code></pre>
  
  <p>-- it will return multiple values like 2|38|45|60 </p>
  
  <pre><code> LV_TRANS_TYPES_IDS := '^(' || LV_TRANS_TYPES_IDS || ')$';
  </code></pre>
  
  <p>-- assignment  ^(2|38|45|60)$ passing this values to the below query</p>
  
  <pre><code> SELECT COUNT(*)
      INTO LN
      FROM MTL_TRANSACTION_TYPES
     WHERE 1 = 1
       AND REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS);
  </code></pre>
  
  <p>first execute the query take the output and use those output for second , third queries input. like (select f1,f2 from table1 where REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS) union all union all select f1,f2 from table3 where REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS)) </p>