====== pears.EssentialSkillDescription ======
<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"."EssentialSkillDescription"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "essentialskill" char(15),in "essentialskillchoicelist" char(100),in "departmentid" char(2) ) 
returns char(200)
begin
  declare "rv" char(200);
  declare "tagdesc" char(25);
  declare "tloc" char(3);
  declare "tid" char(3);
  declare "tcid" char(4);
  declare "i" integer;
  set "i" = "charindex"(';',"essentialskill");
  if "i" > 0 then
    set "tloc" = "left"("essentialskill","i"-1);
    set "essentialskill" = "right"("essentialskill","length"("essentialskill")-"i");
    if "tloc" = 'A' then
      set "tloc" = "string"('A',"departmentid")
    end if end if;
  set "i" = "charindex"(';',"essentialskill");
  if "i" > 0 then
    set "tid" = "left"("essentialskill","i"-1);
    set "tagdesc" = (select first "description" from "tag" where "taglocation" = "tloc" and "tagid" = "tid");
    set "tagdesc" = "ifnull"("tagdesc",'',"tagdesc"+': ');
    set "essentialskill" = "right"("essentialskill","length"("essentialskill")-"i")
  end if;
  set "i" = "charindex"(';',"essentialskill");
  if "i" > 0 then
    set "tcid" = "left"("essentialskill","i"-1)
  else
    set "tcid" = "essentialskill"
  end if;
  if "isnull"("tcid",'') = '' then
    if "isnull"("essentialskillchoicelist",'') = '' then
      return ''
    end if;
    set "essentialskillchoicelist" = ';'+"essentialskillchoicelist"+';';
    set "rv" = (select "list"("description") from "tagchoice" where "taglocation" = "tloc" and "tagid" = "tid" and "locate"("essentialskillchoicelist","string"(';',"tagchoiceid",';')) > 0 and "subchoice" = 0)
  else
    if "tcid" = '%' then
      set "rv" = 'Any'
    else
      set "rv" = (select first "description" from "tagchoice" where "taglocation" = "tloc" and "tagid" = "tid" and "tagchoiceid" = "tcid" and "subchoice" = 0)
    end if end if;
  return "isnull"("tagdesc",'')+"isnull"("rv",'Various')
end
</code>