Well, I knew that you could do a simple if test on two collections like: If COLL_A = COLL_B THEN ...
and JOIN syntax is like " on thinga = thingb " and I knew how to use the COLLECT function. so you put it all together and voilà ! Strange thing is, due to our crappy database design by committee, I needed to do exactly this. Mike On Thu, Jun 25, 2009 at 4:21 PM, Rob Wolfe <rob.wo...@oraclegeeks.com>wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
