Rewrite into SQL

From: amonte <ax.mount_at_gmail.com>
Date: Mon, 9 Mar 2009 22:50:17 +0100
Message-ID: <85c1fb130903091450v3436854au407bb2b3f732bdbe_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 09 2009 - 16:50:17 CDT

Original text of this message