Re: Ramming two SQL queries together.....

From: Walt <walt_askier_at_SHOESyahoo.com>
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

Original text of this message