He's on 8.0.5, Analytic functions are only available in 8.1.x. --- Connor McDonald <[EMAIL PROTECTED]> a �crit�: > 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 <[EMAIL PROTECTED]> 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 > === message truncated ===
===== St�phane Paquette DBA Oracle, consultant entrep�t de donn�es Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___________________________________________________________ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en fran�ais ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
