I stand corrected. Just shows I should not respond to interesting questions while jetlagged in a hotel at Hotsos... On Tue, Mar 10, 2009 at 9:22 AM, amonte <ax.mount@xxxxxxxxx> wrote: > Hi Toon > > I did a small test but I got same results with both approaches > > > SQL> create table t1 > 2 ( > 3 trcseq number, > 4 cntr_no varchar2(10), > 5 update_time date, > 6 flag varchar2(8)); > > Table created. > > SQL> insert into t1 > 2 values > 3 (1, 'Country1', sysdate - 20, 'DELETE'); > > 1 row created. > > SQL> insert into t1 > 2 values > 3 (2, 'Country2', sysdate - 70, 'DELETE'); > > 1 row created. > > SQL> SELECT cntr_no, > 2 max(update_time) arrive_date, > 3 max(trcseq) mysequence > 4 FROM t1 > 5 WHERE flag = 'DELETE' > 6 AND update_time <= sysdate - 60 > 7 GROUP BY cntr_no; > > CNTR_NO ARRIVE_DA MYSEQUENCE > ---------- --------- ---------- > Country2 30-DEC-08 2 > > SQL> SELECT * > 2 FROM t1 y > 3 WHERE trcseq <= (SELECT max(trcseq) > 4 FROM t1 x > 5 where x.cntr_no = y.cntr_no > 6 AND x.update_time <= SYSDATE - 60 > 7 AND x.flag = 'DELETE'); > > TRCSEQ CNTR_NO UPDATE_TI FLAG > ---------- ---------- --------- -------- > 2 Country2 30-DEC-08 DELETE > > > Thanks > > > Alex > > > > On Tue, Mar 10, 2009 at 12:12 AM, Toon Koppelaars < > toon.koppelaars@xxxxxxxxxxx> wrote: > >> I think your rewrite is flawed... >> >> Suppose the table currently has two rows: >> trcseq1 Country1 Tim1 DELETE >> trcseq2 Country2 Tim2 DELETE >> >> Suppose Tim1 is less than 60 days ago. >> Suppose Tim2 is more than 60 days ago. >> Suppose trcseq1 is smaller than trcseq2. >> >> The PL/SQL block will not insert the first row in the other table. >> Your rewrite wil. >> >> Other rewrites all depend upon the constraints that are governing the >> allowed data in that table. >> For instance, if the following condition holds for table Trace_inv: >> >> "for any two different rows in trace_inv, if update_time of the one row is >> smaller than update_time of the other row, then trace_seq of the one row is >> also smaller than trace_seq of the other row" >> (might very well be true for your table) >> >> Then you can rewrite this into. >> >> INSERT INTO backup_trace >> SELECT * >> FROM trace_inv y >> WHERE y.update_time <= SYSDATE - 60 >> AND y.data_field= 'DELETE'; >> >> But then again. It depends. >> >> >> >> >> On Mon, Mar 9, 2009 at 10:50 PM, amonte <ax.mount@xxxxxxxxx> wrote: >> >>> Hi all >>> >>> I have a simple piece of pl/sql code which can be written into SQL, I >>> have rewritten but was wondering if anyone knows a more efficient way to do >>> it? >>> >>> *Original code:* >>> BEGIN >>> FOR i IN ( SELECT cntr_no, >>> max(update_time) arrive_date, >>> max(trace_seq) mysequence >>> FROM trace_inv >>> WHERE data_field= 'DELETE' >>> AND update_time <= sysdate - 60 >>> GROUP BY cntr_no; >>> LOOP >>> INSERT INTO BACKUP_TRACE >>> SELECT * >>> FROM trace_inv >>> WHERE cntr_no = i.cntr_no >>> AND trace_seq <= mysequence; >>> END LOOP; >>> END; >>> / >>> >>> *Rewritten:* >>> INSERT INTO backup_trace >>> SELECT * >>> FROM trace_inv y >>> WHERE trace_seq <= (SELECT max(trace_seq) >>> FROM trace_inv x >>> where x.cntr_no = y.cntr_no >>> AND x.update_time <= SYSDATE - 60 >>> AND x.data_field= 'DELETE'); >>> >>> >>> Thanks all >>> >>> >>> Alex >>> >> >> >> >> -- >> Toon Koppelaars >> RuleGen BV >> +31-615907269 >> Toon.Koppelaars@xxxxxxxxxxx >> www.RuleGen.com >> >> (co)Author: "Applied Mathematics for Database Professionals" >> > > -- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com (co)Author: "Applied Mathematics for Database Professionals"