pears.EssentialSkillDescription

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
}