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