pears.GetQuestGroup

CREATE FUNCTION "pears"."GetQuestGroup"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "stagloc" CHAR(3),IN "stagid" CHAR(10),IN "sparentid" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE "prevtag" CHAR(3);
  DECLARE "multidesc" long VARCHAR;
  DECLARE "comma" CHAR(2);
  DECLARE "staggroup" CHAR(10);
  DECLARE "QUESTGROUPMARKER" CHAR(1);
  SET "QUESTGROUPMARKER" = "char"(127);
  SET "rv" = '';
  SET "staggroup" = '%';
  SET "stagid" = "trim"("isnull"("stagid",''));
  IF "left"("stagid",1) = '*' THEN
    SET "staggroup" = "trim"("right"("stagid","length"("stagid")-1));
    SET "stagid" = '%';
    IF "staggroup" = '' THEN
      SET "staggroup" = '[^-]%'
    END IF END IF;
  SET "prevtag" = '';
  SET "multidesc" = '';
  FOR "fetchfor" AS "fetchcursor" no scroll cursor FOR
    SELECT "tagchoice"."tagchoiceparentid","tagvalue"."tagid","tagvalue"."tagchoiceid","tagvalue"."value","tagchoice"."description","tag"."tagtype",(IF "trim"("isnull"("tag"."units",'')) <> '' THEN ' '+"tag"."units" ELSE '' endif) AS "units",
      (IF "tag"."tagtype" = 'G' THEN
        (SELECT FIRST "description" FROM "tagchoice" AS "s" WHERE "tagvalue"."value" = "s"."value" AND "tagvalue"."taglocation" = "s"."taglocation" AND "tagvalue"."tagid" = "s"."tagid" AND "s"."subchoice" = 1)
      ELSE
        IF "tag"."tagtype" = 'Q' THEN
          (SELECT FIRST "description" FROM "tagchoice" AS "s"
            WHERE "tagvalue"."taglocation" = "s"."taglocation" AND "tagvalue"."tagid" = "s"."tagid"
            AND "s"."tagchoiceid" = any(SELECT "tagchoiceparentid" FROM "tagchoice" AS "C"
              WHERE "tagvalue"."taglocation" = "C"."taglocation" AND "tagvalue"."tagid" = "C"."tagid" AND "C"."tagchoiceid" = "tagvalue"."tagchoiceid"))
        ELSE NULL
        endif
      endif) AS "subchoicedescription" FROM "tagvalue" KEY JOIN("tag","tagchoice") WHERE "tag"."tagtype" LIKE '[LGSQ]'
      AND "tagvalue"."taglocation" = "stagloc" AND "tagvalue"."id" = "sparentid" AND "tagvalue"."tagid" LIKE "stagid" AND "string"("tag"."displaygroup",'') LIKE "staggroup"
      ORDER BY "tag"."sortorder" ASC,"tag"."tagid" ASC,"tagchoice"."sortorder" ASC FOR READ ONLY do
    IF "tagid" <> "prevtag" THEN
      IF "multidesc" <> '' THEN
        SET "rv" = "string"("rv","QUESTGROUPMARKER","prevtag",'DESCRIP',"QUESTGROUPMARKER","multidesc")
      END IF;
      SET "rv" = "string"("rv","QUESTGROUPMARKER","tagid","QUESTGROUPMARKER");
      SET "prevtag" = "tagid";
      SET "multidesc" = '';
      SET "comma" = ''
    END IF;
    IF "tagtype" <> 'Q' THEN
      SET "rv" = "string"("rv","tagchoiceid","char"(9),"value","char"(13),"char"(10));
      SET "multidesc" = "string"("multidesc","comma","description")
    ELSE
      SET "rv" = "string"("rv","tagchoiceparentid","char"(9),"tagchoiceid","char"(13),"char"(10));
      SET "multidesc" = "string"("multidesc","comma","subchoicedescription")
    END IF;
    SET "comma" = ', ';
    CASE "tagtype" WHEN 'G' THEN
      SET "multidesc" = "string"("multidesc",' ',"subchoicedescription") WHEN 'Q' THEN
      SET "multidesc" = "string"("multidesc",' ',"description") WHEN 'S' THEN
      SET "multidesc" = "string"("multidesc",' ',"value","units") WHEN 'L' THEN
      IF "tagchoiceid" = '*' THEN
        SET "multidesc" = "string"("multidesc",' ',"value")
      END IF
    END CASE END FOR;
  IF "multidesc" <> '' THEN
    SET "rv" = "string"("rv","QUESTGROUPMARKER","prevtag",'DESCRIP',"QUESTGROUPMARKER","multidesc")
  END IF;
  RETURN "rv"
END