Join nested table and normal table to fetch the result

Question

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

Justin Cave · Accepted Answer · 2012-04-17T20:16:50.870

4

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.

edited Apr 17 '12 at 20:16

answered Apr 17 '12 at 20:03

Justin Cave

227,342
24
367
384

:My nested table is table of `record type`, and i am doing bulk collect to populate the nested table, all of this ia m doing in pl/sql – Gaurav Soni Apr 17 '12 at 20:06
:I can't use this conversion because i have created nested table inside the stored proc ,so not possible to use convert the list into table (..) – Gaurav Soni Apr 17 '12 at 20:08
@GauravSoni - If the collection is only defined in PL/SQL, you will not be able to use the collection in a SQL statement-- the SQL engine won't be able to get any data about the collection. – Justin Cave Apr 17 '12 at 20:13
:Yes thats what i meant ,Can i create a type as table OF ID only and pass it in table( l_foos(i).ID)?,Is this works? – Gaurav Soni Apr 17 '12 at 20:17
:Actually i don't want to make my collection global , i need to put it into stored procedure only ,then is there any way to find that output in optimized way ,because both the table may contain 10 millions of record – Gaurav Soni Apr 17 '12 at 20:19
@GauravSoni - You could define a collection in SQL, copy the data from the local PL/SQL collection to a local instance of the SQL collection, then use the SQL collection in a SQL statement. That would generally be rather more work than simply defining the entire collection in SQL to begin with however. – Justin Cave Apr 17 '12 at 20:19
@GauravSoni - What is the problem you are trying to solve by not defining the collection in SQL? – Justin Cave Apr 17 '12 at 20:20
There is no problem in defining the collection in SQL ,but i wll sound very foolish here ,because i have never seen any object or type in my database ,so little afraid of defining there .Only 1 object i have found that is looking to be used in many places :) – Gaurav Soni Apr 17 '12 at 20:26
:you have intialized your nested table with new keyword ,but in my existing code .i am doing bulk collect into nested table ,how can i do with my current implementation. – Gaurav Soni Apr 18 '12 at 07:59
@GauravSoni - There is no need to change your implementation. There is no difference between initializing a collection with a `BULK COLLECT` and initializing it explicitly with a `NEW`. – Justin Cave Apr 18 '12 at 08:04

Join nested table and normal table to fetch the result

1 Answers1