CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
GxG
GxG

Reputation: 4771

Using Table Cast

Can you use something on the line of

Select * from table(cast(select * from tab1 inner join tab2)) inner join tab3

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.

I need a simple way to do this preferably without the need for a temporary table.

Thank you.

Database: Oracle 10g

LE:

I have something like 

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 <= length (regexp_replace (str, '[^,]+'))  + 1) t 
       on instr(a.dummy2, t.split) > 0) t2
where a.dummy1='xyz'
group by a.dummy1, a.dummy2

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.

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)

Upvotes: 1

Views: 3201

Answers (2)

GxG
GxG

Reputation: 4771

Yeah... i found the answer... i was just too SQL n00b to see it as it was right in front of me...

i just took the "with" statement outside of the query and it worked.

thank you so much for your help, it was your answer that led me to see my mistake :D

Something like:

with str as 
        (Select '1,2,3,4' from dual)
Select a.dummy1, a.dummy2, wm_concat(t2.dummy3)
    from table1 a, 
    ( 
     Select a.dummy1, t.dummy3 
     from table1 a 
       inner join 
          (Select regexp_substr (str, '[^,]+', 1, rownum) split  
              from str
              connect by level <= length (regexp_replace (str, '[^,]+'))  + 1) t 
       on instr(a.dummy2, t.split) > 0) t2
where a.dummy1='xyz'
group by a.dummy1, a.dummy2

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

CAST seem completely irrelevant here. You use CAST to change the perceived datatype of an expression. Here, you're passing it a result set, not an expression, and you're not saying what datatype to cast to.

You should be able to simply remove the TABLE and CAST calls and do something like:

SELECT * FROM (SELECT * FROM tab1 INNER JOIN tab2 ON ...) INNER JOIN tab3 ON ...

e.g.

SELECT * FROM
  (SELECT d1.dummy FROM dual d1 INNER JOIN dual d2 ON d1.dummy=d2.dummy) d12
   INNER JOIN dual d3 ON d12.dummy = d3.dummy

Subquery factoring should work fine here as well.

WITH x AS (SELECT * FROM DUAL)
SELECT * FROM
 (SELECT d1.dummy FROM x d1 INNER JOIN x d2 ON d1.dummy=d2.dummy) d12
 INNER JOIN dual d3 ON d12.dummy = d3.dummy;

If you're having difficulty getting that kind of construct to work, try adding more detail to your question about specifically what you've tried and what error you're getting.

Upvotes: 4

Related Questions