|
Re: Oracle 11g Ntile function on top of Dense_rank [message #657641 is a reply to message #657639] |
Wed, 16 November 2016 23:36 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
please find the sample code below
CREATE OR REPLACE PROCEDURE "Delete_records_rank_greater_than_one" (
myntilevalue IN NUMBER)
AS
CURSOR cur
IS
SELECT Unique_id
FROM (SELECT Sequence_ID,
NTILE (8) OVER (ORDER BY Customer_ID) myntile
FROM (SELECT Customer_ID,
Unique_id,
Department_ID,
drank
FROM (SELECT tbl.Customer_ID Customer_ID,
tbl.Unique_id Unique_id,
tbl.Department_ID Department_ID,
DENSE_RANK ()
OVER (
PARTITION BY ih.Customer_ID,
ih.Department_ID
ORDER BY ih.Joindate DESC)
AS drank
FROM Emp_Table tbl)
WHERE drank > 1
AND SUBSTR (Department_ID, 1, 7) !=
UPPER ('CSE001_')))
WHERE myntile = myntilevalue;
BEGIN
OPEN cur;
FETCH cur
BULK COLLECT INTO i; --------limit 10000
FORALL c1_rec IN i.FIRST .. i.LAST
DELETE FROM oly_peg_data_owner.pr_data_ih_fact prih
WHERE prih.pxfactid = i (c1_rec);
DBMS_OUTPUT.PUT_LINE (TO_CHAR (SQL%ROWCOUNT) || ' rows deleted');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (
-20001,
'Error_encountered in procedure Delete_records_rank_greater_than_one:: '
|| l_err_msg
|| '>>>>'
|| DBMS_UTILITY.format_error_backtrace,
TRUE);
END;
|
|
|