Rewrite into SQL

From: amonte <>
Date: Mon, 9 Mar 2009 22:50:17 +0100
Message-ID: <>

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:*


        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;
                    INSERT INTO BACKUP_TRACE
                    SELECT *
                    FROM trace_inv
                    WHERE cntr_no = i.cntr_no
                    AND  trace_seq <= mysequence;
        END LOOP;



INSERT INTO backup_trace
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


Received on Mon Mar 09 2009 - 16:50:17 CDT

Original text of this message