pears.EssentialSkillGradeDescription

CREATE FUNCTION "pears"."EssentialSkillGradeDescription"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "essentialskill" CHAR(15),IN "essentialskillgradeid" CHAR(4),IN "departmentid" CHAR(2) ) 
RETURNS CHAR(200)
BEGIN
  DECLARE "rv" CHAR(200);
  DECLARE "tloc" CHAR(3);
  DECLARE "tid" CHAR(3);
  DECLARE "tgid" CHAR(3);
  DECLARE "issubchoice" INTEGER;
  DECLARE "i" INTEGER;
  IF "trim"("isnull"("essentialskillgradeid",'')) = '' THEN
    RETURN ''
  END IF;
  SET "issubchoice" = 1; -- fallback default is graded skill question
  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 "essentialskill" = "right"("essentialskill","length"("essentialskill")-"i")
  END IF;
  SET "i" = "charindex"(';',"essentialskill");
  IF "i" > 0 THEN
    -- Not interested in the skill choiceid
    SET "essentialskill" = "right"("essentialskill","length"("essentialskill")-"i");
    SET "i" = "charindex"(';',"essentialskill");
    IF "i" > 0 THEN
      SET "tgid" = "trim"("left"("essentialskill","i"-1))
    ELSE
      SET "tgid" = "trim"("essentialskill")
    END IF;
    IF "tgid" <> '' THEN
      SET "tid" = "tgid";
      SET "issubchoice" = 0
    END IF END IF;
  SET "rv" = (SELECT FIRST "description" FROM "tagchoice" WHERE "taglocation" = "tloc" AND "tagid" = "tid" AND "tagchoiceid" = "essentialskillgradeid" AND "isnull"("subchoice",0) = "issubchoice");
  RETURN "isnull"("rv","essentialskillgradeid") -- fall back on the literal grade
END