Just curious how long it took you to design this merge that even takes into
account the fact that it may be ran more than once and still have to produce
correct result (that is, necessity of where not exists)? Even minding your
mantra I would resort to PL/SQL for doing this, and it seems that in 8i it's
the only way, using your SQL and merging in a loop using update...
if sql%rowcount=0 then insert... :)
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:aeprsi0m4m_at_drn.newsguy.com...
> In article <aep5im$dqe$1_at_lust.ihug.co.nz>, "Howard says...
> >
> >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?
> >
>
> 9i can do this. We can generate a set that is
>
> a) all records to be inserted (primary key doesn't exist)
> b) all records to be updates (primary key exists and aggflag is 'Y')
>
> we do this by modifying our query to include a "having" clause to get just
> aggregates (having count(*) > 1 -- these are candidates for insertion) or
> singletons whose aggflag is Y (these are ones that need to be updated). Once we
> have those, we'll only keep
>
> a) records that do not already exist, using a where not exists
> b) records that we KNOW we'll be updating (count(*) = 1 records)
>
>
> Then, we merge. Here is the entire thing:
>
>
> ops$tkyte_at_ORA9I.WORLD> select * from t;
>
> AREA SITE FTR PLOT SQUARE_MTR A
> ---------- ---------- ----- ---------- ---------- -
> 1 BRITPK BR1 1 10 Y
> 1 BRITPK BR1 2 10 Y
> 1 BRITPK BR1 3 15 N
> 1 BRITPK BS1 1 5 Y
> 1 BRITPK BS1 2 7 Y
> 1 ANDSO GR1 1 100 Y
> 1 ANDSO GR1 2 120 Y
> 1 ANDSO BR1 1 150 Y
>
> 8 rows selected.
>
> that is what we start with, then we merge:
>
>
> ops$tkyte_at_ORA9I.WORLD>
> ops$tkyte_at_ORA9I.WORLD> merge into t
> 2 using
> 3 ( select *
> 4 from (select area, site, ftr,
> 5 decode( count(*), 1, max( plot ), '9999' ) plot,
> 6 sum(square_mtr) square_mtr, 'N' aggflag,
> 7 count(*) cnt
> 8 from t
> 9 group by area, site, ftr, decode( aggflag, 'Y', 9999, plot )
> 10 having count(*) > 1
> 11 or ( count(*) = 1 and max(aggflag) = 'Y' )
> 12 ) a
> 13 where not exists ( select null
> 14 from t
> 15 where t.area = a.area
> 16 and t.site = a.site
> 17 and t.ftr = a.ftr
> 18 and t.plot = a.plot)
> 19 or cnt = 1
> 20 ) t2
> 21 on ( t.area = t2.area and
> 22 t.site = t2.site and
> 23 t.ftr = t2.ftr and
> 24 t.plot = t2.plot )
> 25 when matched then
> 26 update set t.aggflag = t2.aggflag
> 27 when not matched then
> 28 insert ( area, site, ftr, plot, square_mtr, aggflag )
> 29 values
> 30 ( t2.area, t2.site, t2.ftr, t2.plot, t2.square_mtr, t2.aggflag )
> 31 /
>
> 4 rows merged.
>
> Note that only 4 rows were merged. We in fact had 3 to insert and one to
> update.
>
> ops$tkyte_at_ORA9I.WORLD> select * from t;
>
> AREA SITE FTR PLOT SQUARE_MTR A
> ---------- ---------- ----- ---------- ---------- -
> 1 BRITPK BR1 1 10 Y
> 1 BRITPK BR1 2 10 Y
> 1 BRITPK BR1 3 15 N
> 1 BRITPK BS1 1 5 Y
> 1 BRITPK BS1 2 7 Y
> 1 ANDSO GR1 1 100 Y
> 1 ANDSO GR1 2 120 Y
> 1 ANDSO BR1 1 150 N
> 1 ANDSO GR1 9999 220 N
> 1 BRITPK BR1 9999 20 N
> 1 BRITPK BS1 9999 12 N
>
> 11 rows selected.
>
> I believe that is the set you wanted to generate -- the three new rows and the
> one ANDSO row's aggflag updated...
>
> If we rerun the merge right now:
>
> ops$tkyte_at_ORA9I.WORLD>
> ops$tkyte_at_ORA9I.WORLD> merge into t
> 2 using
> 3 ( select *
> 4 from (select area, site, ftr,
> 5 decode( count(*), 1, max( plot ), '9999' ) plot,
> 6 sum(square_mtr) square_mtr, 'N' aggflag,
> 7 count(*) cnt
> 8 from t
> 9 group by area, site, ftr, decode( aggflag, 'Y', 9999, plot )
> 10 having count(*) > 1
> 11 or ( count(*) = 1 and max(aggflag) = 'Y' )
> 12 ) a
> 13 where not exists ( select null
> 14 from t
> 15 where t.area = a.area
> 16 and t.site = a.site
> 17 and t.ftr = a.ftr
> 18 and t.plot = a.plot)
> 19 or cnt = 1
> 20 ) t2
> 21 on ( t.area = t2.area and
> 22 t.site = t2.site and
> 23 t.ftr = t2.ftr and
> 24 t.plot = t2.plot )
> 25 when matched then
> 26 update set t.aggflag = t2.aggflag
> 27 when not matched then
> 28 insert ( area, site, ftr, plot, square_mtr, aggflag )
> 29 values
> 30 ( t2.area, t2.site, t2.ftr, t2.plot, t2.square_mtr, t2.aggflag )
> 31 /
>
> 0 rows merged.
>
>
> We can see it finds no new rows to insert or existing rows to update...
>
>
> ops$tkyte_at_ORA9I.WORLD> select * from t;
>
> AREA SITE FTR PLOT SQUARE_MTR A
> ---------- ---------- ----- ---------- ---------- -
> 1 BRITPK BR1 1 10 Y
> 1 BRITPK BR1 2 10 Y
> 1 BRITPK BR1 3 15 N
> 1 BRITPK BS1 1 5 Y
> 1 BRITPK BS1 2 7 Y
> 1 ANDSO GR1 1 100 Y
> 1 ANDSO GR1 2 120 Y
> 1 ANDSO BR1 1 150 N
> 1 ANDSO GR1 9999 220 N
> 1 BRITPK BR1 9999 20 N
> 1 BRITPK BS1 9999 12 N
>
> 11 rows selected.
>
>
> >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
> >>
> >
> >
>
> --
> 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 - 08:42:36 CDT
