Hello out there,
I'm looking for a way to get the values of a nested table listagg'ed. Consider this:
create type t_tab is table of number(3);
/
create table test
(id number(3),
id_2 t_tab)
nested table id_2 store as test_id_2 return as locator;
insert into test values (1,null);
insert into test values (2,t_tab());
insert into test values (3,t_tab(2,3));
So now I'm trying this:
select
id,
listagg(n.column_value,', ') within group (order by column_value) v
from
test t left outer join table(t.id_2) n on 1=1
group by
id;
This is what I'm getting:
ID V
------ -------------------
3 2, 3
And what I would expect:
ID V
------ -------------------
1
2
3 2, 3
So can this be achieved? Or do I have to redesign my table using regular relational child tables?
My Oracle Varsion is 12.2.0.1.0.
I tried to google this but did not come up with something useful.
Kind regards,
Lothar