Re: Aggregation Procedure?

No, no... it's looking quite good. I don't think I was entirely clear (though an email I got suggests that it *was* there) -but I have to actually *insert* the aggregates into the table, or physically flip the aggflag of the singleton plots. So just selecting alone doesn't quite do it, as DML of the update and insert flavours is necessary.

My fault for not making it clearer, I guess.

But your approach has got me thinking, which is therefore an unalloyed good.

So many thanks,
HJR "Lothar Armbruester" <lothar.armbruester_at_t-online.de> wrote in message news:PM0003A3B43D98FEA1_at_hades.none.local...
> Howard J. Rogers wrote:
> > 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
> >
> [...]
> >
> > 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.
> >
> Hello Howard, why don't you use plain SQL?
> Here is my suggestion:
>
> select
> area,site,ftr,9999,sum(square_mtr) square_mtr,'N' aggflag
> from
> agg_test
> where
> aggflag='Y'
> group by
> area,site,ftr
> having count(*)>1
> union
> select
> area,site,ftr,plot,square_mtr,aggflag
> from
> agg_test
> where
> aggflag='N'
> union
> select
> area,site,ftr,max(plot) plot,sum(square_mtr) square_mtr,'N' aggflag
> from
> agg_test
> where
> aggflag='Y'
> group by
> area,site,ftr
> having count(*)=1;
>
> First aggregate all rows with aggflag='Y' and count to aggregate >1,
> then add those rows with aggflag='N' and finally add those rows where
> there is only one row to aggregate.
>
> Or did I totally misunderstand your problem?
>
> HTH,
> Lothar
>
> --
> Lothar Armbrüster | la_at_oktagramm.de
> Hauptstr. 26 | la_at_heptagramm.de
> D-65346 Eltville | lothar.armbruester_at_t-online.de
Received on Tue Jun 18 2002 - 14:33:09 CDT