How to select from a colum of a subquery, which is a collection-CodePudding

This query returns a line of 2 column. The second column is a collection

WITH
    s (a, b)
    AS
        (SELECT 1, TO_CLOB ('ff') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 2, TO_CLOB ('ee') FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
SELECT t.* from t where t.a=1;

I would like to change the last line of this query to have a group of lines. The second column wouldn't be a collection, but a club. There would be so many line as the collection as element. In this example:3

I would like to write something like that:

WITH
    s (a, b)
    AS
        (SELECT 1, TO_CLOB ('ff') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 2, TO_CLOB ('ee') FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
SELECT tt.* from (select table(t.b) from t where t.a=1) tt ;

[Error] Execution (34: 23): ORA-00936: missing expression

code

CodePudding user response：

In recent versions of Oracle you can do:

...
select t.a, x.column_value as b
from t
outer apply t.b x
where t.a=1;

 A  B 
--  --
 1  ff
 1  ee
 1  ee

In older versions you could do:

...
select t.a, x.column_value as b
from t
left outer join table(t.b) x on 1=1
where t.a=1;

db<>fiddle