Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to outer join with nested table

Lothar ArmbrüsterSep 16 2020 — edited Sep 16 2020

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

This post has been answered by Paulzip on Sep 16 2020
Jump to Answer
Comments
Post Details
Added on Sep 16 2020
5 comments
956 views