Well, we have made one very big improvement. A while ago we created the position of architect for the BI group and she is now the final authority on all design issues. It is a pretty challenging job considering that she has opinionated types like myself (reporting & dashboarding), my director and our DW manager to deal with but she has developed a good stiff backbone and is good at saying no to the dumbest of our fabulous (to us) ideas.
Highly recommended to do this if you have the opportunity! it is almost as good as starting from scratch. Anyway, ask me in december 2010 how it worked out. If On Thu, June 25, 2009 19:37, Michael Moore wrote: > 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 -~----------~----~----~----~------~----~------~--~---
