Re: join on collections

[Michael Moore]

I probably should have just done this in the first place, but here it is ..
--------------------------------------------------------------------

Performing a JOIN on groups of values using nested
tables<http://plsqlnotes.blogspot.com/2008/07/use-sum-function-but-save-detail-from.html>

-- Join on a nested table example:

-- this example takes 2 tables (c & d). On each table it does
-- a group by, and the details of that group are collected into a nested
table.
-- tables c & d are then joined on that nested table

-- I probably would never do this, but I do think it is interesting.

--DROP TABLE D CASCADE CONSTRAINTS;

CREATE TABLE d (animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO d (animal, grp) VALUES ('cat', 4);
INSERT INTO d (animal, grp) VALUES ('cat', 7);
INSERT INTO d (animal, grp) VALUES ('toad', 7);
INSERT INTO d (animal, grp) VALUES ('frog', 4);
INSERT INTO d (animal, grp) VALUES ('frog', 7);
COMMIT ;

-- DROP TABLE c CASCADE CONSTRAINTS;

CREATE TABLE c( animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;
INSERT INTO c (animal, grp) VALUES ('cat', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 1);
INSERT INTO c (animal, grp) VALUES ('frog', 66);
INSERT INTO c (animal, grp) VALUES ('cat', 33);
INSERT INTO c (animal, grp) VALUES ('cat', 44);
INSERT INTO c (animal, grp) VALUES ('cat', 66);
INSERT INTO c (animal, grp) VALUES ('frog', 44);
INSERT INTO c (animal, grp) VALUES ('toad', 44);
COMMIT ;

SELECT grp, animal FROM c ORDER BY grp, animal;
SELECT grp, animal FROM d ORDER BY grp, animal;

CREATE OR REPLACE TYPE string20_table IS TABLE OF VARCHAR2(20);

DECLARE
mystr VARCHAR2(80);
BEGIN
FOR cur1 IN
(SELECT *
    FROM
    (SELECT grp grp1, CAST(COLLECT(animal) AS string20_table) tab1
         FROM c GROUP BY grp)
    JOIN
    (SELECT grp grp2, CAST(COLLECT(animal) AS string20_table) tab2
        FROM d GROUP BY grp)
    ON tab1 = tab2  -- the magic happens here
)
  LOOP
    DBMS_OUTPUT.put_line( TO_CHAR(cur1.grp1)
        || '='
        || TO_CHAR(cur1.grp2));
  END LOOP;
END;
-- final output
-- 1=4
-- 44=7
-- 66=4

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---