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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 25 May 2010 08:27:56 -0700 (PDT)
Message-ID: <b16ca0f7-7ca5-4a56-90b0-698a96c1ac32_at_q8g2000vbm.googlegroups.com>



On May 25, 11:07 am, "Mr.Frog.to...._at_googlemail.com" <mr.frog.to...._at_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. Received on Tue May 25 2010 - 10:27:56 CDT

Original text of this message