Re: Rewrite into SQL

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"

--
http://www.freelists.org/webpage/oracle-l