1

I use this example only because you can reproduct it easily. I already know that I could use the aggregate function count for this particular example. But the table t, that I'm using is already created and the column b is a collection.

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.count()
from t

ORA-00904: "T"."B"."COUNT": invalid identifier

I have tried with a lot a collection methods but to no avail.

code

this question is similar to How to select the first element of a collection being a column of a query.

In this question I've already proposed a way to avoid using a collection method. Therefore, what I want to know is if it is possible to use a collection method and if yes how?

What I proposed in the similar question is cumbersom, if you have a easier way to do, I would be glad too.

0 Answers0