pears.IQXNetTemplateGradeList

CREATE FUNCTION "pears"."IQXNetTemplateGradeList"( 
  IN "templateid" CHAR(20),IN "deptid" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE "xtagid" CHAR(20);
  DECLARE "xchoiceid" CHAR(20);
  DECLARE "xlocation" CHAR(20);
  DECLARE "xgradetagid" CHAR(20);
  DECLARE "xgradeid" CHAR(20);
  SELECT FIRST "trim"("isnull"("tagid",'')),"trim"("isnull"("tagchoiceid",'')),"trim"("taglocation"),"trim"("isnull"("gradetagid",'')),"trim"("isnull"("essentialskillgradeid",'')) INTO "xtagid",
    "xchoiceid","xlocation","xgradetagid","xgradeid" FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "templateid";
  IF "xlocation" = 'A' THEN
    SET "xlocation" = "string"('A',"deptid")
  END IF;
  IF "xgradetagid" = '' THEN
    SET "xgradetagid" = "xtagid"
  END IF;
  SET "rv" = NULL;
  IF "xgradetagid" <> '' AND "xgradeid" = '' THEN
    SELECT "list"("string"("c"."tagchoiceid",']~[',"c"."description"),']~[' ORDER BY "isnull"("c"."value","c"."sortorder") ASC) INTO "rv" FROM "tagchoice" AS "c" KEY JOIN "tag" AS "t"
      WHERE "c"."taglocation" = "xlocation" AND "c"."tagid" = "xgradetagid" AND("c"."subchoice" = 1 OR "t"."tagtype" = 'M')
  END IF;
  RETURN "rv"
END