CODEWITHSUNDEEP
oracle-databasejoinplsqloracle11gr2nested-table
Gaurav Soni
Gaurav Soni

Reputation: 6338

Join nested table and normal table to fetch the result

I am having a normal table temp and a nested table temp_nt

Temp
-------------
 ID    Status
-------------
 1     open
 2     close
 3     open
 4     open
 5     close
---------------

Suppose my nested table is having list of ID, X

Lets say the data in nested table is like 

temp_nt(1).ID=1 temp_nt(1).X='ANC'
temp_nt(2).ID=2 temp_nt(2).X='pqr'
temp_nt(3).ID=3 temp_nt(3).X='ANCF'
temp_nt(4).ID=4 temp_nt(4).X='ANCF'

Can it be possible to join both to get the data like below,

   Status   COUNT
 -----------------------
    open     3
    close    1
 -----------------------

Since ID=5 is not present in the nested table, therefore it is excluded from the count

Upvotes: 1

Views: 5668

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

It would help to define exactly what objects you're working with...

You have a table with 5 rows of data

SQL> create table foo(
  2    id number,
  3    status varchar2(10)
  4  );

Table created.

SQL> insert into foo values( 1, 'open' );

1 row created.

SQL> insert into foo values( 2, 'close' );

1 row created.

SQL> insert into foo values( 3, 'open' );

1 row created.

SQL> insert into foo values( 4, 'open' );

1 row created.

SQL> insert into foo values( 5, 'close' );

1 row created.

But then how is your nested table defined? Is it defined in SQL or PL/SQL? Are you using the object from SQL or PL/SQL?

If you have defined the nested table in SQL

SQL> create type foo_obj is object (
  2    id number,
  3    status varchar2(10)
  4  );
  5  /

Type created.

SQL> create type foo_nt
  2      as table of foo_obj;
  3  /

Type created.

And you are using the nested table in PL/sQL, you can use the TABLE operator

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_foos foo_nt := new foo_nt();
  3  begin
  4    l_foos.extend(4);
  5    l_foos(1) := new foo_obj( 1, 'ANC' );
  6    l_foos(2) := new foo_obj( 2, 'pqr' );
  7    l_foos(3) := new foo_obj( 3, 'ANCF' );
  8    l_foos(4) := new foo_obj( 4, 'ANCF' );
  9    for x in (select t.status, count(*) cnt
 10                from foo t,
 11                     table( l_foos ) l
 12               where t.id = l.id
 13               group by t.status)
 14    loop
 15      dbms_output.put_line( x.status || ' ' || x.cnt );
 16    end loop;
 17* end;
SQL> /
close 1
open 3

PL/SQL procedure successfully completed.

Is that what you're looking for? Or do you have a different setup?

If you are defining a local collection in PL/SQL, you won't be able to use that collection in a SQL statement since the SQL engine isn't able to access any information about the collection type. If you want to use the collection in SQL, it would make much more sense to define the collection in SQL.

Upvotes: 4

Related Questions