pears.RemoveRD1Dups [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.RemoveRD1Dups ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."RemoveRD1Dups"()
begin
  declare "MyCur" dynamic scroll cursor for select "ID","D1_Date","d1_safetime","D1_User","D1_Type","appcli" from "R_D1" order by 2 asc,3 asc,4 asc,5 asc,6 asc for update;
  declare @ContactEventID char(20);
  declare @ContactDate date;
  declare @LastContactDate date;
  declare @ContactTime time;
  declare @LastContactTime time;
  declare @StaffID char(20);
  declare @LastStaffID char(20);
  declare @ClassCode char(2);
  declare @LastClassCode char(2);
  declare @PersonID char(20);
  declare @LastPersonID char(20);
  declare "Counter" bigint;
  declare "DeleteCounter" integer;
  set "Counter" = 1;
  set "DeleteCounter" = 0;
  message "string"('DeDup Started ',current timestamp) to console;
  if not exists(select * from "sysindex" where "index_name" = 'XXXChangeCRSRD1DeDupIndexXXX') then
    message 'Creating Index' to console;
    create index "XXXChangeCRSRD1DeDupIndexXXX" on "R_D1"("D1_Date" asc,"D1_SafeTime" asc,"D1_User" asc,"D1_Type" asc,"appcli" asc)
  end if;
  open "MyCur" with hold;
  fetch first "MyCur" into @ContactEventID,@ContactDate,@ContactTime,@StaffID,@ClassCode,@PersonID;
  "ContactLoop":
  while(sqlcode = 0) loop
    if(@LastContactDate = @ContactDate and @LastContactTime = @ContactTime and @LastStaffID = @StaffID and @LastClassCode = @ClassCode and @LastPersonID = @PersonID) then
      update "r_d1" set "exclude" = 1 where current of "MyCur";
      set "DeleteCounter" = "DeleteCounter"+1
    end if;
    set @LastContactDate = @ContactDate;
    set @LastContactTime = @ContactTime;
    set @LastStaffID = @StaffID;
    set @LastClassCode = @ClassCode;
    set @LastPersonID = @PersonID;
    if(select "remainder"("Counter",10000)) = 0 then
      message "string"("Counter",' Contact Events Checked') to console
    end if;
    set "counter" = "counter"+1;
    fetch next "MyCur" into @ContactEventID,@ContactDate,@ContactTime,@StaffID,@ClassCode,
      @PersonID
  end loop "ContactLoop";
  close "MyCur";
  message "string"("DeleteCounter",' Records Excluded') to console;
  message "string"('DeDup Finished ',current timestamp) to console
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."RemoveRD1Dups" IS 
{create procedure RemoveRD1Dups()
begin
  declare MyCur cursor for select ID, D1_Date, d1_safetime, D1_User, D1_Type, appcli from R_D1 order by 2,3,4,5,6 for update;
  declare @ContactEventID char(20);
  declare @ContactDate date; 
  declare @LastContactDate date;
  declare @ContactTime time; 
  declare @LastContactTime time;
  declare @StaffID char(20); 
  declare @LastStaffID char(20);
  declare @ClassCode char(2); 
  declare @LastClassCode char(2);
  declare @PersonID char(20);
  declare @LastPersonID char(20);
  declare Counter bigint;
  declare DeleteCounter	int;
  set Counter = 1;
  set DeleteCounter = 0;
  Message string('DeDup Started ',Current TimeStamp);
  if not exists (select * from sysindex where index_name = 'XXXChangeCRSRD1DeDupIndexXXX') then 
   message 'Creating Index';
   create index XXXChangeCRSRD1DeDupIndexXXX on R_D1(D1_Date asc , D1_SafeTime asc , D1_User asc , D1_Type asc, appcli asc )
  end if;
  Open MyCur with hold;
  fetch first MyCur into @ContactEventID, @ContactDate, @ContactTime, @StaffID, @ClassCode, @PersonID;
  ContactLoop:
  while (sqlcode = 0 ) loop
    if ( @LastContactDate = @ContactDate and @LastContactTime = @ContactTime and @LastStaffID = @StaffID and @LastClassCode = @ClassCode and @LastPersonID = @PersonID )
   then
    update r_d1 set exclude =1 where current of MyCur;
	set DeleteCounter = DeleteCounter + 1;
    end if;
    set @LastContactDate = @ContactDate;
    set @LastContactTime = @ContactTime;
    set @LastStaffID = @StaffID;
    set @LastClassCode = @ClassCode;
	set @LastPersonID = @PersonID;
	if (select remainder(Counter,10000)) = 0 then 
	message string(Counter, ' Contact Events Checked');
		end if;
    set counter = counter + 1;
    fetch next MyCur into @ContactEventID, @ContactDate, @ContactTime, @StaffID, @ClassCode, @PersonID;
  end loop ContactLoop;
  Close MyCur;
  Message string(DeleteCounter,' Records Excluded');
  Message string('DeDup Finished ',Current TimeStamp);
end
}
</code>