Re: Ramming two SQL queries together.....
Date: Wed, 26 May 2010 10:36:04 -0400
Message-ID: <plaLn.458658$vr1.390295_at_en-nntp-07.dc1.easynews.com>
Mr.Frog.to.you_at_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 Received on Wed May 26 2010 - 09:36:04 CDT