Apologies for not reading your post thoroughly
enough..
:-(
Cheers
Connor
- Jack van Zanen <nlzanen1_at_EY.NL> wrote: > Didn't
they come in with Oracle 8i?
>
> I could not find anything analytical in the online
> documentation (than
> again couldn't find the 8.0.5 warehouse guide
> either)
>
> I'm unfortunately on 8.0.5 for this one
>
>
> Jack
>
>
>
>
>
> Connor McDonald
>
>
> <hamcdc_at_yahoo.co. To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> uk> cc:
> (bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
>
> Sent by:
> Subject: Re: Rolling sums (?)
>
> root_at_fatcity.com
>
>
>
>
>
>
>
>
> 15-08-2002 16:28
>
>
> Please respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
> Take a look at the windowing functions under
> 'ana;ytic
> functions' in the data warehouse guide...They are
> very
> very cool..
>
> hth
> connor
>
> --- Jack van Zanen <nlzanen1_at_EY.NL> wrote: > Maybe
> having a bad hairday but following code will
> > give me for every period
> > number the sum of all work in progress for the
> > period plus the previous 12
> > periods. Basically it takes the period number from
> > the current record and
> > sums a column of that record with the previous 12
> > periods.
> >
> > year period value
> > 2000 1 1
> > 2000 2 2
> > 2000 3 3
> > 2000 4 4
> > 2000 5 5
> > 2000 6 6
> > 2000 7 7
> > 2000 8 8
> > 2000 9 9
> > 2000 10 10
> > 2000 11 11
> > 2000 12 12
> > 2001 1 13
> > 2001 2 14
> > 2001 3 15
> >
> > So for period 2001 1 I need the values 1 thru 13
> > added together (91)
> > Period 2001 2 needs to be the values 2 thru 14
> added
> > together (104)
> > etc....
> >
> > As you maybe can imagine the explain plan for this
> > baby is 4 full
> > tablescans on quite large tables. (see below)
> >
> > My question is if somebody has a better solution
> to
> > handle this query?????
> >
> > Oracle 8.0.5 !!!
> > AIX 4.3.3
> >
> >
> > SELECT V1.ENGAGEMENT
> > , V1.YEAR
> > , V1.PERIOD
> > , MAX(V1.NET_FEE_EARNED_PTD)
> > , MAX(V1.EXP_WIP_VAL_PTD)
> > , SUM(V2.TIME_WIP_VAL)
> > , MAX(V1.GNRL_WON)
> > , MAX(V1.GNRL_WOFF)
> > , MAX(V1.TIME_WOFF)
> > , MAX(V1.WIP_PROV)
> > , MAX(V1.EXP_WOFF)
> > , MAX(V1.DB_WOFF)
> > , MAX(V1.DB_PROV)
> > FROM
> > (
> > select max(WSTAT.ENG_NUMB) AS
> ENGAGEMENT,
> > max(WSTAT.FNANCL_YEAR) AS YEAR,
> > max(WSTAT.PRD_NUMB) AS PERIOD,
> > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> > + SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> > - SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> > -
> SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> > 0))
> > - SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> > - SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD,
> > 0))
> > - SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> > - SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> > NET_FEE_EARNED_PTD,
> > SUM(NVL(WSTAT.EXP_WIP_VAL_PTD,0))
> > EXP_WIP_VAL_PTD,
> > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> > TIME_WIP_VAL,
> > SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> > GNRL_WON,
> > SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> > GNRL_WOFF,
> > SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> > 0)) TIME_WOFF,
> > SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> > WIP_PROV,
> > SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD,
> 0))
> > EXP_WOFF,
> > SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> > DB_WOFF,
> > SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> > DB_PROV
> > FROM ENG_WIP_STATS WSTAT,
> > ENG_DBTRS_STATS DSTAT
> > WHERE WSTAT.ENG_NUMB(+)=DSTAT.ENG_NUMB
> > AND WSTAT.FNANCL_YEAR=DSTAT.FNANCL_YEAR
> > AND WSTAT.PRD_NUMB=DSTAT.PRD_NUMB
> > GROUP BY WSTAT.ENG_NUMB,
> > WSTAT.FNANCL_YEAR,
> > WSTAT.PRD_NUMB
> > ) v1,
> > (
> > select max(WSTAT.ENG_NUMB) AS
> ENGAGEMENT,
> > max(WSTAT.FNANCL_YEAR) AS YEAR,
> > max(WSTAT.PRD_NUMB) AS PERIOD,
> > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> > + SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> > - SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> > -
> SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> > 0))
> > - SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> > - SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD,
> > 0))
> > - SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> > - SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> > NET_FEE_EARNED_PTD,
> > SUM(NVL(WSTAT.EXP_WIP_VAL_PTD,0))
> > EXP_WIP_VAL_PTD,
> > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> > TIME_WIP_VAL,
> > SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> > GNRL_WON,
> > SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> > GNRL_WOFF,
> > SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> > 0)) TIME_WOFF,
> > SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> > WIP_PROV,
> > SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD,
> 0))
> > EXP_WOFF,
> > SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> > DB_WOFF,
> > SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> > DB_PROV
> > FROM ENG_WIP_STATS WSTAT,
> > ENG_DBTRS_STATS DSTAT
> > WHERE WSTAT.ENG_NUMB(+)=DSTAT.ENG_NUMB
> > AND WSTAT.FNANCL_YEAR=DSTAT.FNANCL_YEAR
> > AND WSTAT.PRD_NUMB=DSTAT.PRD_NUMB
> > GROUP BY WSTAT.ENG_NUMB,
> > WSTAT.FNANCL_YEAR,
> > WSTAT.PRD_NUMB
> > ) V2
> > WHERE v1.ENGAGEMENT=V2.ENGAGEMENT(+)
> > AND TO_DATE(V2.YEAR(+)||V2.PERIOD(+),'YYYYMM')
> >=
> >
> ADD_MONTHS(TO_DATE(V1.YEAR||V1.PERIOD,'YYYYMM'),-12)
> > AND TO_DATE(V2.YEAR(+)||V2.PERIOD(+),'YYYYMM')
> <=
> > TO_DATE(V1.YEAR||V1.PERIOD,'YYYYMM')
> > GROUP BY V1.ENGAGEMENT
> > , V1.YEAR
> > , V1.PERIOD
> >
> >
> >
> > Explain plan:
> >
> > ID PID QUERY_PLAN
> > ----- -----
> >
>
>
> > 0 SELECT STATEMENT Cost = 12531921
> > 1 0 SORT GROUP BY Card=62754915
> > 2 1 MERGE JOIN OUTER Card=62754915
> > 3 2 SORT JOIN Card=1584360
> > 4 3 VIEW Card=1584360
> > 5 4 SORT GROUP BY Card=1584360
> > 6 5 FILTER Card=
> > 7 6 MERGE JOIN OUTER Card=
> > 8 7 SORT JOIN
> Card=1354977
> > 9 8 TABLE ACCESS FULL
> > ENG_DBTRS_STATS
> > Card=1354977
> > 10 7 SORT JOIN
> Card=1956365
> > 11 10 TABLE ACCESS FULL
> > ENG_WIP_STATS Card=1956365
> > 12 2 FILTER Card=
> > 13 12 SORT JOIN Card=
> > 14 13 VIEW Card=1584360
> > 15 14 SORT GROUP BY
> Card=1584360
> > 16 15 FILTER Card=
> > 17 16 MERGE JOIN OUTER
> Card=
> > 18 17 SORT JOIN
> > Card=1354977
> > 19 18 TABLE ACCESS FULL
> > ENG_DBTRS_STATS
> > Card=1354977
> > 20 17 SORT JOIN
> > Card=1956365
> > 21 20 TABLE ACCESS FULL
> > ENG_WIP_STATS
> > Card=1956365
> >
> >
> > THX
> >
> >
>
> > De informatie verzonden in dit e-mailbericht is
> > vertrouwelijk en is
> > uitsluitend bestemd voor de geadresseerde.
> > Openbaarmaking,
> > vermenigvuldiging, verspreiding en/of verstrekking
> > van deze informatie aan
> > derden is, behoudens voorafgaande schriftelijke
> > toestemming van Ernst &
> > Young, niet toegestaan. Ernst & Young staat niet
> in
> > voor de juiste en
> > volledige overbrenging van de inhoud van een
> > verzonden e-mailbericht, noch
> > voor tijdige ontvangst daarvan. Ernst & Young kan
> > niet garanderen dat een
> > verzonden e-mailbericht vrij is van virussen, noch
> > dat e-mailberichten
> > worden overgebracht zonder inbreuk of tussenkomst
> > van onbevoegde derden.
> >
> > Indien bovenstaand e-mailbericht niet aan u is
> > gericht, verzoeken wij u
> > vriendelijk doch dringend het e-mailbericht te
> > retourneren aan de verzender
> > en het origineel en eventuele kopieën te
> verwijderen
> > en te vernietigen.
> >
> > Ernst & Young hanteert bij de uitoefening van haar
> > werkzaamheden algemene
> > voorwaarden, waarin een beperking van
> > aansprakelijkheid is opgenomen. De
> > algemene voorwaarden worden u op verzoek kosteloos
> > toegezonden.
> >
>
> > The information contained in this communication is
> > confidential and is
> > intended solely for the use of the individual or
> > entity to whom it is
> > addressed. You should not copy, disclose or
> > distribute this communication
> > without the authority of Ernst & Young. Ernst &
> > Young is neither liable for
> > the proper and complete transmission of the
> > information contained in this
> > communication nor for any delay in its receipt.
> > Ernst & Young does not
> > guarantee that the integrity of this communication
> > has been maintained nor
> > that the communication is free of viruses,
> > interceptions or interference.
> >
> > If you are not the intended recipient of this
> > communication please return
> > the communication to the sender and delete and
> > destroy all copies.
> >
> > In carrying out its engagements, Ernst & Young
> > applies general terms and
> > conditions, which contain a clause that limits its
> > liability. A copy of
> > these terms and conditions is available on request
> > free of charge.
> >
>
> >
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jack van Zanen
> > INET: nlzanen1_at_EY.NL
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
>
> "Remember amateurs built the ark - Professionals
> built the Titanic"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
>
>
> De informatie verzonden in dit e-mailbericht is
> vertrouwelijk en is
> uitsluitend bestemd voor de geadresseerde.
> Openbaarmaking,
> vermenigvuldiging, verspreiding en/of verstrekking
> van deze informatie aan
> derden is, behoudens voorafgaande schriftelijke
> toestemming van Ernst &
> Young, niet toegestaan. Ernst & Young staat niet in
> voor de juiste en
> volledige overbrenging van de inhoud van een
> verzonden e-mailbericht, noch
> voor tijdige ontvangst daarvan. Ernst & Young kan
> niet garanderen dat een
> verzonden e-mailbericht vrij is van virussen, noch
> dat e-mailberichten
> worden overgebracht zonder inbreuk of tussenkomst
> van onbevoegde derden.
>
> Indien bovenstaand e-mailbericht niet aan u is
> gericht, verzoeken wij u
> vriendelijk doch dringend het e-mailbericht te
> retourneren aan de verzender
> en het origineel en eventuele kopieën te verwijderen
> en te vernietigen.
>
> Ernst & Young hanteert bij de uitoefening van haar
> werkzaamheden algemene
> voorwaarden, waarin een beperking van
> aansprakelijkheid is opgenomen. De
> algemene voorwaarden worden u op verzoek kosteloos
> toegezonden.
>
> The information contained in this communication is
> confidential and is
> intended solely for the use of the individual or
> entity to whom it is
> addressed. You should not copy, disclose or
> distribute this communication
> without the authority of Ernst & Young. Ernst &
> Young is neither liable for
> the proper and complete transmission of the
> information contained in this
> communication nor for any delay in its receipt.
> Ernst & Young does not
> guarantee that the integrity of this communication
> has been maintained nor
> that the communication is free of viruses,
> interceptions or interference.
>
> If you are not the intended recipient of this
> communication please return
> the communication to the sender and delete and
> destroy all copies.
>
> In carrying out its engagements, Ernst & Young
> applies general terms and
> conditions, which contain a clause that limits its
> liability. A copy of
> these terms and conditions is available on request
> free of charge.
>
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jack van Zanen
> INET: nlzanen1_at_EY.NL
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 15 2002 - 12:08:34 CDT