Where did you every come up with the idea to do that? I like it, but it wouldn't have occured to me in a million years.
Rob On Thu, June 25, 2009 19:08, Michael Moore wrote: > 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 -~----------~----~----~----~------~----~------~--~---
