# Re: Rewrite into SQL

Date: Tue, 10 Mar 2009 09:46:12 +0100

Message-ID: <ecf3dae70903100146k25e5e24ai2785b02344d02d8d_at_mail.gmail.com>

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_at_gmail.com> 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_at_rulegen.com> 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_at_gmail.com> 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_at_RuleGen.com*

*>> www.RuleGen.com*

*>>*

*>> (co)Author: "Applied Mathematics for Database Professionals"*

*>>*

*>*

*>*

-- Toon Koppelaars RuleGen BV +31-615907269 Toon.Koppelaars_at_RuleGen.com www.RuleGen.com (co)Author: "Applied Mathematics for Database Professionals" -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 10 2009 - 03:46:12 CDT