Ramming two SQL queries together.....

From: Mr.Frog.to.you_at_googlemail.com <mr.frog.to.you_at_googlemail.com>
Date: Tue, 25 May 2010 08:07:33 -0700 (PDT)
Message-ID: <571c5f83-614b-4959-b134-2a1599633b2f_at_m33g2000vbi.googlegroups.com>

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 Received on Tue May 25 2010 - 10:07:33 CDT