Re: Aggregation Procedure?

HI Thomas,

Appreciate this, since it's small, compact and effective. Trouble is, I would like to actually insert a new 9999-plot row if appropriate, or update the existing plot's Y agflag to N if it's a singleton. Not just report on the same... do real DML. Which is why I though PL/SQL would be required.

Any further insights?

Regards
HJR "Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:aeo0sl02qvu_at_drn.newsguy.com...
> In article <aen6t2$kdc$1_at_lust.ihug.co.nz>, "Howard says...
> >
> >Calling all PL/SQL gurus (since I'm not one).
> >
> >Here's my data:
> >
> >AREA SITE FTR Plot SQUARE_MTR AGGFLAG
> >1 BRITPK BR1 1 10.00 Y
> >1 BRITPK BR1 2 10.00 Y
> >1 BRITPK BR1 3 15.00 N
> >1 BRITPK BS1 1 5.00 Y
> >1 BRITPK BS1 2 7.00 Y
> >1 ANDSO GR1 1 100.00 Y
> >1 ANDSO GR1 2 120.00 Y
> >1 ANDSO BR1 1 150.00 Y
> >
> >In other words, sites contain multiple features. The Primary Key is
> >area||site||ftr||plot.
> >
> >I want to write a procedure that aggregates similar features on a site
into
> >a single 'aggregate plot', provided the real features' agflags are set to
Y.
> >Aggregate plots are identified with a '9999' plot number, and an agflag
of
> >"N" (since aggregates can't themselves be aggregated). Plots which are
not
> >to be aggregated (or which can't be because there is nothing to aggregate
> >them with) just have their Aggflag set to "N".
> >
> >Aggregating the above data following that rule should yield the following
> >(missing out the original plots for clarity's sake, unless they are
> >non-aggregateable):
> >
> >AREA SITE FTR Plot SQUARE_MTR AGGFLAG
> >1 BRITPK BR1 3 15.00 N
> >1 BRITPK BR1 9999 20.00 N
> >1 BRITPK BS1 9999 12.00 N
> >1 ANDSO GR1 9999 220.00 N
> >1 ANDSO BR1 1 150.00 N
> >
> >Note Plot 3 on BRITPK escaped aggregation because its aggflag was "N".
The
> >BR1 on ANDSO also escaped, because it was the only plot of that feature
on
> >the site.
> >
> >I wrote something in Visual Basic (!!) years ago to achieve something
> >similar, and it involved searching through the table in AREA+SITE+FTR
order,
> >noting when there was a change, and bunging in the totals where
appropriate.
> >
> >But I'd like to do it in PL/SQL... so if anyone has any ideas, I'd be
> >grateful.
> >
> >Regards
> >HJR
> >
> >
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select area,
> 2 site,
> 3 ftr,
> 4 decode( count(*), 1, max( plot ), '9999' ) plot,
> 5 sum(square_mtr),
> 6 'N'
> 7 from t
> 8 group by area, site, ftr, decode( aggflag, 'Y', 9999, plot )
> 9 /
>
> AREA SITE FTR PLOT SUM(SQUARE_MTR) '
> ---------- ---------- ----- ---------- --------------- -
> 1 ANDSO BR1 1 150 N
> 1 ANDSO GR1 9999 220 N
> 1 BRITPK BR1 3 15 N
> 1 BRITPK BR1 9999 20 N
> 1 BRITPK BS1 9999 12 N
>
> may not be intuitive ;)
>
> but it works...
>
> we group by area, site, ftr AND IF aggflag=Y -- a constant, else the rest
of the
> primary key. That "folds" all of the records to be aggregatd together.
Then,
> we can use
>
> 4 decode( count(*), 1, max( plot ), '9999' ) plot,
>
>
> to either
>
> o return 9999 if we actually aggregated.
> o max(plot) -- which is just really plot -- if we did not
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Wed Jun 19 2002 - 00:41:13 CDT