pears.NetOwnerAggregateSearch [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.NetOwnerAggregateSearch ======
<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"."NetOwnerAggregateSearch"( in "pWebUserID" char(20),in "SearchValue" char(20) default '%',in "SearchCategory" char(5) default null,in "pSlice" integer default 1,in "pSliceSize" integer default 20 ) 
result( "ObjectID" char(20),"ObjectName" char(60),"Town" char(100),"JobTitle" char(60),"Status" char(10),"companyName" char(60),"Alert" char(255),"IsFavourite" integer ) 
begin
  if "length"("isnull"("SearchValue",'')) = 0 then
    select '~','','','','','','',0;
    return
  end if;
  select "Employmentid","name","address","jobtitle",'Contact' as "status","companyname","alert","isfavourite" from "pears"."NetOwnerContactSearch"("pWebUserID" = "pWebUserID","SearchValue" = "SearchValue","pSlice" = "pSlice","pSliceSize" = "pSliceSize") union
  select "companyid","name","address",'' as "Jobtitle",'Company' as "status",'',"alert","isfavourite" from "pears"."NetOwnerCompanySearch"("pWebUserID" = "pWebUserID","SearchValue" = "SearchValue","pSlice" = "pSlice","pSliceSize" = "pSliceSize") union
  select "personid","name","address",'' as "Jobtitle",'Candidate' as "status",'',"alert","isfavourite" from "pears"."NetOwnerCandidateSearch"("pWebUserID" = "pWebUserID","SearchValue" = "SearchValue","pSlice" = "pSlice","pSliceSize" = "pSliceSize")
    order by "name" asc
end /* DOC
2016-09-19 PC alter routine to allow search, and paging

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerAggregateSearch" IS 
{create PROCEDURE pears."NetOwnerAggregateSearch"(in "pWebUserID" char(20),in "SearchValue" char(20) default '%',in SearchCategory char(5) default null,in pSlice integer default 1, in pSliceSize integer default 20 )
RESULT(ObjectID char(20),ObjectName char(60),Town char(100),JobTitle char(60),Status char(10),companyName char(60),Alert char(255),IsFavourite integer )
BEGIN
    if length(isnull(SearchValue,''))=0 then
        select '~','','','','','','',0;
        return;
    end if;
   select Employmentid,name,address,jobtitle,'Contact' as status,companyname,alert,isfavourite from "pears"."NetOwnerContactSearch"("pWebUserID" = pWebUserID,"SearchValue" = SearchValue,"pSlice" = pSlice,"pSliceSize" = pSliceSize)
   union
   select companyid,name,address,'' as Jobtitle,'Company' as status,'',alert,isfavourite from  "pears"."NetOwnerCompanySearch"("pWebUserID" = pWebUserID,"SearchValue" = SearchValue,"pSlice" = pSlice,"pSliceSize" = pSliceSize)
   union
   select personid,name,address,'' as Jobtitle,'Candidate' as status,'',alert,isfavourite from "pears"."NetOwnerCandidateSearch"("pWebUserID" = pWebUserID,"SearchValue" = SearchValue,"pSlice" = pSlice,"pSliceSize" = pSliceSize)
   order by name asc
END

/* DOC
2016-09-19 PC alter routine to allow search, and paging

*/
}
</code>