pears.NetFavouriteTitle [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.NetFavouriteTitle ======
<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 function "pears"."NetFavouriteTitle"( in "pNodeID" char(20) ) 
returns char(250)
begin
  declare "rv" char(250);
  select(case "type"
    when 'C' then(select "string"("name",', ',"addr1",', ',"postcode") from "company" where "companyid" = "favourites"."id")
    when 'E' then(select "string"("p"."name",', ',"c"."name") from "employment" as "e" key join("company" as "c","person" as "p") where "e"."employmentid" = "favourites"."id")
    when 'P' then(select "name" from "person" where "personid" = "favourites"."id")
    when 'V' then(select "v"."position"+' - '+"c"."name" from "vacancy" as "v" key join "employment" key join "company" as "c" where "vacancyid" = "favourites"."id")
    when 'L' then(select "pe"."name"+' as '+"v"."position"+' at '+"c"."name" from "placement" key join "employment" as "em" key join "person" as "pe","placement" key join "vacancy" as "v" key join "employment" key join "company" as "c" where "placementid" = "favourites"."id")
    else "Description"
    end) into "rv" from "Favourites" where "FavouritesID" = "pNodeID";
  return "rv"
end /* TEST
call NetTestSetup('');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F1',null,null,'TEST','Favourites','H');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F2','F1','TEST','TEST','TestPerson','P');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F3','F1','TEST','TEST','TestPerson','C');
insert into favourites (FavouritesID,Parent,ID,StaffID,Description,Type) on existing update values ('F4','F1','TEST','TEST','TestPerson','E');
update company set addr1='AddressLine',postcode='XX1 1XX' where companyid='TEST';
select NetFavouriteTitle('F1');
expect Favourites;
expect EOF;
select NetFavouriteTitle('F2');
expect test;
expect EOF;
select replace(NetFavouriteTitle('F3'),',','!');
expect test! AddressLine! XX1 1XX;
expect EOF;
select replace(NetFavouriteTitle('F4'),',','!');
expect test! test;
expect EOF;
select replace(NetFavouriteTitle('F5'),',','!');
expect;
expect EOF;
update company set addr1='',postcode='' where companyid='TEST';
*/
/* DOC
2016-08-11 V1.2 Vacancy line and Placement line added
*/
</code>