====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."EssentialSkillDescription" IS 
{create function 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>