title: Using Table Cast
body: <p>Can you use something on the line of</p>
  
  <pre><code>Select * from table(cast(select * from tab1 inner join tab2)) inner join tab3
  </code></pre>
  
  <p>Take into account that what's inside the table(cast()) is something much more complex than a simple select involving a block like with test as (select) select *... etc.</p>
  
  <p>I need a simple way to do this preferably without the need for a temporary table.</p>
  
  <p>Thank you.</p>
  
  <p>Database: Oracle 10g</p>
  
  <p>LE:</p>
  
  <p>I have something like </p>
  
  <pre><code>Select a.dummy1, a.dummy2, wm_concat(t2.dummy3)
      from table1 a, 
      (with str as 
          (Select '1,2,3,4' from dual) 
       Select a.dummy1, t.dummy3 
       from table1 a 
         inner join 
            (Select regexp_substr (str, '[^,]+', 1, rownum) split  
                from str
                connect by level &lt;= length (regexp_replace (str, '[^,]+'))  + 1) t 
         on instr(a.dummy2, t.split) &gt; 0) t2
  where a.dummy1='xyz'
  group by a.dummy1, a.dummy2
  </code></pre>
  
  <p>The main idea is that column t2.dummy3 contains CSVs. Thats why i have select '1,2,3,4' from dual.
  I need to find all rows that contain at least one of the values from str.</p>
  
  <p>Using any kind of loop is out of the question because further i need to integrate this into a larger query used for a report in SSRS, and the tables needed for this are quite large (>1mil rows)</p>