pears.IQXNetTemplateChoiceList

CREATE FUNCTION "pears"."IQXNetTemplateChoiceList"( 
  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);
  SELECT FIRST "trim"("isnull"("tagid",'')),"trim"("isnull"("tagchoiceid",'')),"taglocation" INTO "xtagid","xchoiceid","xlocation" FROM "tempshifttemplate" WHERE "tempshifttemplateid" = "templateid";
  IF "xlocation" = 'A' THEN
    SET "xlocation" = "string"('A',"deptid")
  END IF;
  SET "rv" = NULL;
  IF "xtagid" <> '' AND "xchoiceid" = '' THEN
    SELECT "list"("string"("tagchoiceid",']~[',"description"),']~[' ORDER BY "sortorder" ASC,"description" ASC) INTO "rv" FROM "tagchoice"
      WHERE "taglocation" = "xlocation" AND "tagid" = "xtagid" AND "isnull"("subchoice",0) = 0
  END IF;
  RETURN "rv"
END