Ramming two SQL queries together.....
Date: Tue, 25 May 2010 08:07:33 -0700 (PDT)
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.....
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.PRODUKTwhere 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..............
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_TYPgroup 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.
The Frog Received on Tue May 25 2010 - 10:07:33 CDT