NTILE Function
SELECT PYSUBJECTID,
IHAGGREGATEKEY,
PXOUTCOMETIME
FROM
(SELECT PYSUBJECTID,
IHAGGREGATEKEY,
PXOUTCOMETIME,
DRANK
FROM
(SELECT IH.PYSUBJECTID PYSUBJECTID,
IH.IHAGGREGATEKEY IHAGGREGATEKEY,
IH.PXOUTCOMETIME PXOUTCOMETIME,
DENSE_RANK() OVER (PARTITION BY IH.PYSUBJECTID, IH.IHAGGREGATEKEY ORDER BY IH.PXOUTCOMETIME DESC) AS drank,
NTILE(4) OVER (ORDER BY IH.PYSUBJECTID) myntile
FROM XYZ.ABC
)
WHERE DRANK > 1
AND myntile = 1
)
==
SELECT pxfactid
FROM
(SELECT
pxfactid,
ntile(8) over(ORDER BY pysubjectid) myntile
FROM
(SELECT ysubjectid, pxfactid,
drank
FROM
(SELECT ih.pysubjectid pysubjectid, ih.pxfactid pxfactid,
dense_rank() over(PARTITION BY ih.pysubjectid, ih.ihaggregatekey ORDER BY ih.pxoutcometime DESC) AS drank
FROM xyz.abc ih)
WHERE drank > 1)
)
WHERE myntile = myntilevalue;
==========
create or replace PROCEDURE delete_Data ( tablename IN VARCHAR2, nrows IN number ) IS
V_Cur_Campaigncode Varchar2(500);
V_Cur_Nbapriority number;
V_Cur_del UROWID;
Cur_Campaigncode Sys_Refcursor;
Cur_Nbapriority Sys_Refcursor;
Cur_del Sys_Refcursor;
Sqlstring Varchar2(500);
Rows_To_Delete Number;
Rows_to_count Number;
Tab_Name Varchar2(500);
The_Sql Varchar(500);
The_Count Number;
Rows_To_Cnt Number;
Begin
Rows_To_delete :=0;
Begin
Open Cur_Campaigncode For 'Select Campaigncode, Nbapriority From ' || Tablename || ' Where Allocatedbranch = 999999 Group By Campaigncode, Nbapriority order by Campaigncode, Nbapriority ' ;
Loop
Fetch Cur_Campaigncode Into V_Cur_Campaigncode, V_Cur_Nbapriority;
Exit When Cur_Campaigncode%Notfound;
The_Sql := 'select count(*) from ' || Tablename || ' Where Campaigncode= ' || '''' || V_Cur_Campaigncode || '''' || ' and Allocatedbranch = 999999';
Execute Immediate The_Sql Into The_Count;
If The_Count >= 0 then --Is Not Null Then
Rows_To_Cnt := The_Count;
Else
Rows_To_Cnt := 0;
End If;
Rows_To_Count := Rows_To_Cnt - Nrows;
If Rows_To_Count >= 0 Then --Is Not Null Then
Rows_To_Delete := Rows_To_Count;
Else
Rows_To_Delete := 0;
End If;
Rows_To_Delete := Rows_To_Delete + 1;
Open Cur_Del For 'Select FST.Rowid From ' || Tablename || ' Fst , ( Select Campaigncode, Nbapriority From '|| Tablename || ' Where Campaigncode= '||''''|| V_Cur_Campaigncode ||''''||' And Allocatedbranch = 999999 Order By Campaigncode asc, Nbapriority asc) Sec ' || ' Where Fst.Campaigncode =Sec.Campaigncode and fst.Nbapriority = Sec.Nbapriority And Rownum < ' || Rows_To_Delete ;
LOOP
Fetch Cur_Del Into V_Cur_Del;
Exit When Cur_Del%Notfound;
Sqlstring := 'Delete From ' || Tablename || ' Where ROWid = ' || '''' || V_Cur_Del || '''' ;
Dbms_Output.Put_Line(Sqlstring);
Execute Immediate Sqlstring;
Commit;
End Loop;
End Loop;
Commit;
EXCEPTION
When Others Then
Raise_Application_Error(-20001, 'Error encountered while deleting the data', True);
End;
End delete_Data;
==========
create or replace
PROCEDURE delete_Data
( tablename IN VARCHAR2, nrows IN number ) IS
V_Cur_Campaigncode Varchar2(500);
V_Cur_Nbapriority number;
V_Cur_del UROWID;
Cur_Campaigncode Sys_Refcursor;
Cur_Nbapriority Sys_Refcursor;
Cur_del Sys_Refcursor;
Sqlstring Varchar2(500);
Rows_To_Delete Number;
Rows_to_count Number;
Tab_Name Varchar2(500);
The_Sql Varchar(500);
The_Count Number;
Rows_To_Cnt Number;
Begin
Rows_To_delete :=0;
Begin
Open Cur_Campaigncode For 'Select Campaigncode, Nbapriority From ' || Tablename || ' Where Allocatedbranch = 999999 Group By Campaigncode, Nbapriority order by Campaigncode, Nbapriority ' ;
Loop
Fetch Cur_Campaigncode Into V_Cur_Campaigncode, V_Cur_Nbapriority;
Exit When Cur_Campaigncode%Notfound;
The_Sql := 'select count(*) from ' || Tablename || ' Where Campaigncode= ' || '''' || V_Cur_Campaigncode || '''' || ' and Allocatedbranch = 999999';
Execute Immediate The_Sql Into The_Count;
If The_Count >= 0 then --Is Not Null Then
Rows_To_Cnt := The_Count;
Else
Rows_To_Cnt := 0;
End If;
Rows_To_Count := Rows_To_Cnt - Nrows;
If Rows_To_Count >= 0 Then --Is Not Null Then
Rows_To_Delete := Rows_To_Count;
Else
Rows_To_Delete := 0;
End If;
Rows_To_Delete := Rows_To_Delete + 1;
Open Cur_Del For 'Select FST.Rowid From ' || Tablename || ' Fst , ( Select Campaigncode, Nbapriority From '|| Tablename || ' Where Campaigncode= '||''''|| V_Cur_Campaigncode ||''''||' And Allocatedbranch = 999999 Order By Campaigncode asc, Nbapriority asc) Sec ' || ' Where Fst.Campaigncode =Sec.Campaigncode and fst.Nbapriority = Sec.Nbapriority And Rownum < ' || Rows_To_Delete ;
LOOP
Fetch Cur_Del Into V_Cur_Del;
Exit When Cur_Del%Notfound;
Sqlstring := 'Delete From ' || Tablename || ' Where ROWid = ' || '''' || V_Cur_Del || '''' ;
Dbms_Output.Put_Line(Sqlstring);
Execute Immediate Sqlstring;
Commit;
End Loop;
End Loop;
Commit;
EXCEPTION
When Others Then
Raise_Application_Error(-20001, 'Error encountered while deleting the data', True);
End;
End delete_Data;
No comments:
Post a Comment