pears.GetQuestLongWebText

CREATE FUNCTION "pears"."GetQuestLongWebText"( 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","longdescription","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"+"longdescription"+"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 FIRST("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 FIRST("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 "description" FROM "tagchoice" WHERE "taglocation" = "tagloc" AND "tagid" = "toptagid" AND "tagchoiceid" = "x"),'')
    ELSE
      SET "i" = 0;
      FOR "fetch2for" AS "fetch2cursor" no scroll cursor FOR
        SELECT "tagchoice"."description" AS "tagchdesc","tagvalue"."tagchoiceid","tagvalue"."value" AS "tgval" 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;
        SET "rv" = "rv"+"tagchdesc";
        CASE "tagtype"
        WHEN 'L' THEN IF "tagchoiceid" = '*' THEN
            SET "rv" = "rv"+' '+"string"("tgval")
          END IF WHEN 'S' THEN SET "rv" = "rv"+' '+"string"("tgval")+"xunits"
        WHEN 'G' THEN SELECT "max"("description") INTO "xdesc" FROM "tagchoice" WHERE "taglocation" = "tagloc" AND "tagid" = "toptagid" AND "value" = "tgval" AND "subchoice" = 1;
          IF "isnull"("xdesc",'') <> '' THEN
            SET "rv" = "rv"+' '+"xdesc"
          END IF
        END CASE;
        SET "i" = "i"+1
      END FOR
    END CASE;
    SET "rv" = "rv"+"newline" END FOR;
  RETURN("rv")
END