Also, here is a blog post I wrote a while back. It's not exactly what you want, but pay attention to the COLLECT statement with the GROUB BY clause. You will want to use something like this.
http://plsqlnotes.blogspot.com/search/label/powermultiset On Sun, Jan 8, 2012 at 12:02 PM, Michael Moore <michaeljmo...@gmail.com>wrote: > This is what you need. Take a look and if you still have questions, let me > know. > http://www.oracle-developer.net/display.php?id=303 > > > On Sun, Jan 8, 2012 at 3:10 AM, Sathya Narayanan <cred...@gmail.com>wrote: > >> In our application the following data model constitutes to one of the >> most expensive queries in the database. >> The question is this: find the item_groups that has a given property in a >> given store >> >> A given item group belongs to a store if both of the following conditions >> are satisfied >> 1. All the items in the item group should belong to the store in >> ITEM_LIST_IN_STORE. >> 2. All the items in the item group should have a translation in the >> ITEM_NAME for the country/language to which the store belongs >> >> I have tried several ways to answer this question and have always ended >> up thinking there should be a better way to answer this. >> I have found it literally impossible to do this without generating a >> cartesian product at some point in the query. >> I am placing this problem in this forum in the hope that some of you may >> be able to figure out a better solution than me. >> >> CREATE TABLE ITEM_LIST_IN_STORE >> ( >> ITEM_NO VARCHAR2(15), >> STORE_NO VARCHAR2(5), >> DELETE_DATE DATE >> ) >> / >> >> CREATE TABLE ITEM_GROUPS >> ( >> ITEM_GROUP_NO VARCHAR2(15), >> ITEM_NO_CHILD VARCHAR2(15) >> ) >> / >> >> CREATE TABLE ITEM_NAME >> ( >> ITEM_NO VARCHAR2(15), >> ITEM_NAME VARCHAR2(55), >> CTY_CODE VARCHAR2(5), >> LANG_CODE VARCHAR2(5), >> DELETE_DATE DATE >> ) >> / >> >> CREATE TABLE ITEM_PROPS >> ( >> ITEM_NO VARCHAR2(15), >> ITEM_NAME_OFFICIAL VARHCAR2(55), >> PRODUCT_NAME_NO VARHCAR2(55), >> PRODUCT_TYPE_NO VARCHAR2(55) >> ) >> / >> >> CREATE TABLE STORE_CTY_LANG >> ( >> STORE_NO VARCHAR2(5), >> CTY_CODE VARCHAR2(5), >> LANG_CODE VARCHAR2(5) >> DELETE_DATE DATE >> ) >> / >> >> -- >> 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 >> > > -- 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
