2

the table t has two column: a and b. a is an integer, b is collection.

I would like to to select for each line a and the first value of the collection b.

I tried this but it doesn't work

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         group BY s.a)
select t.a, t.b.first()
from t

[Error] Execution (42: 16): ORA-00904: "T"."B"."FIRST": invalid identifier

Is there a way to to do that?

code

Pierre-olivier Gendraud
  • 1,545
  • 1
  • 8
  • 33
  • Does this answer your question? [How to Use COLLECT with VARCHAR2 Oracle 10g](https://stackoverflow.com/questions/37423515/how-to-use-collect-with-varchar2-oracle-10g) –  Apr 26 '22 at 06:13
  • @Kendle not really. I've already created a collection. What I can't, is to use a colleciton method inside of a sql block – Pierre-olivier Gendraud Apr 26 '22 at 06:25

1 Answers1

1

this query do the same things witout the collection method first.

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b)
    AS
        (SELECT t.a, tb.b
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE     b
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
SELECT *
  FROM v;

code

Pierre-olivier Gendraud
  • 1,545
  • 1
  • 8
  • 33
  • 1
    More simple - no `v` subfactor and no scalar subquery needed. Also eliminates redundant `JOIN` from `t` to `t`: `select t.a, tb.first_b from t outer apply (SELECT column_value first_b FROM TABLE(t.b) FETCH FIRST 1 ROW ONLY) tb` – Matthew McPeak Apr 26 '22 at 15:20
  • @MatthewMcPeak Thanks for the improvement. I will change the my answer – Pierre-olivier Gendraud Apr 27 '22 at 05:23