====== pears.EssentialSkillGradeDescription ======
<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"."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
</code>