ick (on the non-design design) and nifty. I am about to go live on a shiny new 11g data warehouse and we are determined to not make any of the same mistakes we made on the previous one. In other words, we are going to make new and more exciting ones...
Rob On Thu, June 25, 2009 19:27, Michael Moore wrote: > 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 -~----------~----~----~----~------~----~------~--~---
