pears.EssentialSkillDescription

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