Lol, good luck with that. On Thu, Jun 25, 2009 at 4:34 PM, Rob Wolfe <rob.wo...@oraclegeeks.com>wrote:
> > 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 -~----------~----~----~----~------~----~------~--~---
