Aggregation Procedure?

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 Received on Tue Jun 18 2002 - 06:51:27 CDT