Ramming two SQL queries together.....
in [Oracle]
|
Prev: Rman backup of BCV using ASM
Next: CLOB
From: Mr.Frog.to.you on 25 May 2010 11:07 Hi Everyone, I am wanting to ask some advice before attmepting this as I am afraid I might tie up too much resource on our DWH - and that would tick off the admins (understandably). I have two SQL statements that provide me with data, both very similar and based off the same tables (one uses only a subset of the tables). One of the queries returns a block of data telling me how many times a product is seen in a given group of stores, while the second tells me how many stores are in the given group of stores. The end idea is to have a calculated column that gives me a 'distribution' percentile - take the number of times the product is seen in the given group and divide it by the number fo stores in the given group (and multiply by 100 of course). The problem I have is that I cannot create views (not allowed). This leaves me with having to do this in a 'single step'. I would not normally approach a problem this way, and not being an Oracle expert I hoping that I can gain the benefit of others experiences in approaching this. I am really hoping to avoid doing this as a two stepper in MS Access or something......... The SQL statements are as follows: 1/ The one with the products seen..... select V.VS_NAME as Channel, T.NAME as Organisation, VT.VS_TYP_NAME as Type, CAST(P.VBE_EAN as VARCHAR(13)) as EAN, COUNT(L.GELISTET) as Listed from MASTERGISD.VERTRIEBSSCHIENE V join MASTERGISD.TRADE_ORG_MASTER T on V.MAIN_ORG = T.ORG_NBR join MASTERGISD.VERTRIEBSSCH_TYP VT on VT.VS_TYP = V.VS_TYP join MASTERGISD.LISTKOPF LK on LK.VS_NR = V.VS_NR join MASTERGISD.LISTPROD L on L.HDRSYSID = LK.SYSID join MASTERGISD.PRODUKT P on P.PRODUKT = L.PRODUKT where P.SEGMENT = 'PETCARE' group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME, P.VBE_EAN order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME ________________________________________________________________ 2/ The one with the count of the stores.............. select V.VS_NAME as Store, Count(V.VS_NAME) as Stores, T.NAME as Organisation, VT.VS_TYP_NAME as Type from MASTERGISD.VERTRIEBSSCHIENE V join MASTERGISD.TRADE_ORG_MASTER T on V.MAIN_ORG = T.ORG_NBR join MASTERGISD.VERTRIEBSSCH_TYP VT on VT.VS_TYP = V.VS_TYP group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME _____________________________________________________________________ The perfect end result would be to have all the fields in the first query with simply an added field from the second with the storecount (stores). Is this possible in a single step? I was thinking of using a correlated subquery but I am concerned that this would take inordinate amounts of time due to how correlated subqueries work (once for each row......) Any guidance would be greatly appreciated. Cheers The Frog
From: Charles Hooper on 25 May 2010 11:27 On May 25, 11:07 am, "Mr.Frog.to....(a)googlemail.com" <mr.frog.to....(a)googlemail.com> wrote: > Hi Everyone, > > I am wanting to ask some advice before attmepting this as I am afraid > I might tie up too much resource on our DWH - and that would tick off > the admins (understandably). > > I have two SQL statements that provide me with data, both very similar > and based off the same tables (one uses only a subset of the tables). > One of the queries returns a block of data telling me how many times a > product is seen in a given group of stores, while the second tells me > how many stores are in the given group of stores. The end idea is to > have a calculated column that gives me a 'distribution' percentile - > take the number of times the product is seen in the given group and > divide it by the number fo stores in the given group (and multiply by > 100 of course). > > The problem I have is that I cannot create views (not allowed). This > leaves me with having to do this in a 'single step'. I would not > normally approach a problem this way, and not being an Oracle expert I > hoping that I can gain the benefit of others experiences in > approaching this. I am really hoping to avoid doing this as a two > stepper in MS Access or something......... > (snip) > The perfect end result would be to have all the fields in the first > query with simply an added field from the second with the storecount > (stores). Is this possible in a single step? I was thinking of using a > correlated subquery but I am concerned that this would take inordinate > amounts of time due to how correlated subqueries work (once for each > row......) > > Any guidance would be greatly appreciated. > > Cheers > > The Frog Inline views will probably be the solution for you. First, getting rid of the ANSI syntax so that you can see what is happening. The first SQL statement: select V.VS_NAME as Channel, T.NAME as Organisation, VT.VS_TYP_NAME as Type, CAST(P.VBE_EAN as VARCHAR(13)) as EAN, COUNT(L.GELISTET) as Listed from MASTERGISD.VERTRIEBSSCHIENE V, MASTERGISD.TRADE_ORG_MASTER T, MASTERGISD.VERTRIEBSSCH_TYP VT, MASTERGISD.LISTKOPF LK, MASTERGISD.LISTPROD L, MASTERGISD.PRODUKT P where P.SEGMENT = 'PETCARE' AND V.MAIN_ORG = T.ORG_NBR AND VT.VS_TYP = V.VS_TYP AND LK.VS_NR = V.VS_NR AND L.HDRSYSID = LK.SYSID AND P.PRODUKT = L.PRODUKT group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME, P.VBE_EAN order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME; The second SQL statement: select V.VS_NAME as Store, Count(V.VS_NAME) as Stores, T.NAME as Organisation, VT.VS_TYP_NAME as Type from MASTERGISD.VERTRIEBSSCHIENE V MASTERGISD.TRADE_ORG_MASTER T MASTERGISD.VERTRIEBSSCH_TYP VT WHERE V.MAIN_ORG = T.ORG_NBR AND VT.VS_TYP = V.VS_TYP group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME; The combined SQL statement: select V.VS_NAME as Channel, T.NAME as Organisation, VT.VS_TYP_NAME as Type, CAST(P.VBE_EAN as VARCHAR(13)) as EAN, COUNT(L.GELISTET) as Listed, VW.STORES from MASTERGISD.VERTRIEBSSCHIENE V, MASTERGISD.TRADE_ORG_MASTER T, MASTERGISD.VERTRIEBSSCH_TYP VT, MASTERGISD.LISTKOPF LK, MASTERGISD.LISTPROD L, MASTERGISD.PRODUKT P, (select V.VS_NAME as Store, Count(V.VS_NAME) as Stores, T.NAME as Organisation, VT.VS_TYP_NAME as Type from MASTERGISD.VERTRIEBSSCHIENE V MASTERGISD.TRADE_ORG_MASTER T MASTERGISD.VERTRIEBSSCH_TYP VT WHERE V.MAIN_ORG = T.ORG_NBR AND VT.VS_TYP = V.VS_TYP group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME) VW where P.SEGMENT = 'PETCARE' AND V.MAIN_ORG = T.ORG_NBR AND VT.VS_TYP = V.VS_TYP AND LK.VS_NR = V.VS_NR AND L.HDRSYSID = LK.SYSID AND P.PRODUKT = L.PRODUKT AND V.VS_NAME=VW.STORE AND T.NAME=VW.ORGANISATION group by V.VS_NAME, T.NAME, VT.VS_TYP_NAME, P.VBE_EAN, VW.STORES order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME; In the above SQL statement I used the second SQL statement just as if it were another table simply by putting the SQL statement in the FROM clause, wrapped in () and given an alias. You aliased a couple of the columns in the SQL statement, and so I joined the first query to the second using those column aliases (AND V.VS_NAME=VW.STORE AND T.NAME=VW.ORGANISATION). Those are the basics of using inline views. Charles Hooper Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table" http://hoopercharles.wordpress.com/ IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: Walt on 26 May 2010 10:36 Mr.Frog.to.you(a)googlemail.com wrote: > Hi Everyone, > The perfect end result would be to have all the fields in the first > query with simply an added field from the second with the storecount > (stores). Is this possible in a single step? Charles' example of an inline view should work for you. You could also use a correlated subquery. Something like select V.VS_NAME as Channel, T.NAME as Organisation, VT.VS_TYP_NAME as Type, CAST(P.VBE_EAN as VARCHAR(13)) as EAN, COUNT(L.GELISTET) as Listed, ( selectCount(V.VS_NAME) from /*tables */ WHERE /*appropriate joins to outer query*/ ) as storecount from MASTERGISD.VERTRIEBSSCHIENE V, MASTERGISD.TRADE_ORG_MASTER T, MASTERGISD.VERTRIEBSSCH_TYP VT, MASTERGISD.LISTKOPF LK, MASTERGISD.LISTPROD L, MASTERGISD.PRODUKT P, where P.SEGMENT = 'PETCARE' AND V.MAIN_ORG = T.ORG_NBR AND VT.VS_TYP = V.VS_TYP AND LK.VS_NR = V.VS_NR AND L.HDRSYSID = LK.SYSID AND P.PRODUKT = L.PRODUKT AND V.VS_NAME=VW.STORE AND T.NAME=VW.ORGANISATION order by V.VS_NAME, T.NAME, VT.VS_TYP_NAME; (Note that the above won't work as written, but the
From: Mr.Frog.to.you on 28 May 2010 03:18 Thankyou both gentlemen. I appreciate the feedback you have given me. I am not an Oracle expert so I was not sure what the best approach was. I will set both up and see which runs faster (I am guessing the inline due to the way correlated subqueries run). I thank you both very much. Cheers The Frog
From: joel garry on 28 May 2010 13:23
On May 28, 12:18 am, "Mr.Frog.to....(a)googlemail.com" <mr.frog.to....(a)googlemail.com> wrote: > Thankyou both gentlemen. I appreciate the feedback you have given me. > I am not an Oracle expert so I was not sure what the best approach > was. I will set both up and see which runs faster (I am guessing the > inline due to the way correlated subqueries run). > > I thank you both very much. > > Cheers > > The Frog I'm wondering about how you think correlated subqueries run. See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries008.htm http://jonathanlewis.wordpress.com/2007/03/08/transformation-and-optimisation/ If you haven't already, get Jonathan's book about the optimizer. See this example: http://jonathanlewis.wordpress.com/2006/11/06/filter-subqueries/ jg -- @home.com is bogus. http://www.networkworld.com/news/2010/052710-alleged-100m-scareware-sellers-facing.html |