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
END;
/
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-lReceived on Mon Mar 09 2009 - 16:50:17 CDT
