pears.GetQuestTextLong

CREATE FUNCTION "pears"."GetQuestTextLong"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "tagloc" CHAR(3),IN "wildtagid" CHAR(3),IN "parentid" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "rv" long VARCHAR;
  DECLARE "tagval" DOUBLE;
  DECLARE "textval" long VARCHAR;
  DECLARE "x" CHAR(4);
  DECLARE "y" INTEGER;
  DECLARE "xunits" CHAR(11);
  DECLARE "xdesc" CHAR(25);
  DECLARE "i" SMALLINT;
  DECLARE "tab" CHAR(1);
  DECLARE "newline" CHAR(2);
  SET "tab" = "char"(9);
  SET "newline" = "char"(13)+"char"(10);
  SET "rv" = '';
  FOR "fetchfor" AS "fetchcursor" no scroll cursor FOR
    SELECT "tagid" AS "toptagid",(IF "isnull"("longdescription",'') = '' THEN "description" ELSE "longdescription" endif) AS "tagdesc","tagtype","units" FROM "tag" WHERE "taglocation" = "tagloc" AND "tagid" LIKE "wildtagid" ORDER BY "sortorder" ASC FOR READ ONLY do
    SET "xunits" = "isnull"("units",'');
    IF "xunits" <> '' THEN
      SET "xunits" = ' '+"xunits"
    END IF;
    SET "rv" = "rv"+"tagdesc"+"tab";
    CASE "tagtype" WHEN 'N' THEN
      SELECT "max"("value") INTO "tagval" FROM "tagvalue" WHERE "taglocation" = "tagloc" AND "id" = "parentid" AND "tagid" = "toptagid";
      IF "isnull"("tagval",0) <> 0 THEN
        SET "rv" = "rv"+"string"("tagval")+"xunits"
      END IF WHEN 'D' THEN
      SELECT "max"("value") INTO "tagval" FROM "tagvalue" WHERE "taglocation" = "tagloc" AND "id" = "parentid" AND "tagid" = "toptagid";
      IF "isnull"("tagval",0) <> 0 THEN
        SET "y" = "tagval"; //// Following expression does NOT behave WITH a DOUBLE
        SET "rv" = "rv" || "dateformat"("date"('1899-12-30')+"y",'dd/mm/yyyy')
      END IF WHEN 'T' THEN
      SELECT "max"("textvalue") INTO "textval" FROM "tagvalue" WHERE "taglocation" = "tagloc" AND "id" = "parentid" AND "tagid" = "toptagid";
      IF "textval" IS NOT NULL THEN
        SET "rv" = "rv"+"textval"
      END IF WHEN 'U' THEN
      SELECT "max"("textvalue") INTO "textval" FROM "tagvalue" WHERE "taglocation" = "tagloc" AND "id" = "parentid" AND "tagid" = "toptagid";
      IF "textval" IS NOT NULL THEN
        SET "rv" = "rv"+"textval"
      END IF WHEN 'M' THEN
      SELECT "max"("tagchoiceid") INTO "x" FROM "tagvalue" WHERE "taglocation" = "tagloc" AND "id" = "parentid" AND "tagid" = "toptagid";
      IF "x" IS NULL THEN
        SET "x" = '_'
      END IF;
      SET "rv" = "rv"+"isnull"((SELECT(IF "isnull"("longdescription",'') = '' THEN "description" ELSE "longdescription" endif) FROM "tagchoice" WHERE "taglocation" = "tagloc" AND "tagid" = "toptagid" AND "tagchoiceid" = "x"),'')
    ELSE
      SET "i" = 0;
      FOR "fetch2for" AS "fetch2cursor" no scroll cursor FOR
        SELECT(IF "isnull"("tagchoice"."longdescription",'') = '' THEN "tagchoice"."description" ELSE "tagchoice"."longdescription" endif) AS "tagchdesc","tagvalue"."tagchoiceid","tagvalue"."value" AS "tgval","tagchoice"."tagchoiceparentid" AS "tcpid"
          FROM "tagvalue" KEY JOIN "tagchoice"
          WHERE "tagvalue"."taglocation" = "tagloc" AND "tagvalue"."tagid" = "toptagid" AND "tagvalue"."id" = "parentid" ORDER BY "tagchoice"."sortorder" ASC FOR READ ONLY do
        IF "i" > 0 THEN
          SET "rv" = "rv"+', '
        END IF;
        CASE "tagtype" WHEN 'L' THEN
          SET "rv" = "rv"+"tagchdesc";
          IF "tagchoiceid" = '*' THEN
            SET "rv" = "rv"+' '+"string"("tgval")
          END IF WHEN 'S' THEN
          SET "rv" = "rv"+"tagchdesc"+' '+"string"("tgval")+"xunits" WHEN 'G' THEN
          SELECT "max"((IF "isnull"("longdescription",'') = '' THEN "description" ELSE "longdescription" endif)) INTO "xdesc" FROM "tagchoice" WHERE "taglocation" = "tagloc" AND "tagid" = "toptagid" AND "value" = "tgval" AND "subchoice" = 1;
          IF "isnull"("xdesc",'') <> '' THEN
            SET "rv" = "rv"+"tagchdesc"+' '+"xdesc"
          END IF WHEN 'Q' THEN
          SELECT "max"((IF "isnull"("longdescription",'') = '' THEN "description" ELSE "longdescription" endif)) INTO "xdesc" FROM "tagchoice" WHERE "taglocation" = "tagloc" AND "tagid" = "toptagid" AND "tagchoiceid" = "tcpid" AND "subchoice" = 0;
          IF "isnull"("xdesc",'') <> '' THEN
            SET "rv" = "rv"+"xdesc"+' '+"tagchdesc"
          END IF
        END CASE;
        SET "i" = "i"+1
      END FOR
    END CASE;
    SET "rv" = "rv"+"newline" END FOR;
  RETURN("rv")
END