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.
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.