pears.NetLongQuestAnswer

CREATE FUNCTION "pears"."NetLongQuestAnswer"( 
  IN "ptagloc" CHAR(3),IN "ptagid" 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 "xtagtype" CHAR(1);
  SET "rv" = '';
  SELECT "tagtype","trim"("isnull"("units",'')) INTO "xtagtype","xunits" FROM "tag" WHERE "taglocation" = "ptagloc" AND "tagid" = "ptagid";
  IF "xunits" <> '' THEN
    SET "xunits" = ' '+"xunits"
  END IF;
  CASE "xtagtype" WHEN 'N' THEN
    SELECT "max"("value") INTO "tagval" FROM "tagvalue" WHERE "taglocation" = "ptagloc" AND "id" = "parentid" AND "tagid" = "ptagid";
    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" = "ptagloc" AND "id" = "parentid" AND "tagid" = "ptagid";
    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" = "ptagloc" AND "id" = "parentid" AND "tagid" = "ptagid";
    IF "textval" IS NOT NULL THEN
      SET "rv" = "rv"+"textval"
    END IF WHEN 'U' THEN
    SELECT FIRST "textvalue" INTO "textval" FROM "tagvalue" WHERE "taglocation" = "ptagloc" AND "id" = "parentid" AND "tagid" = "ptagid";
    IF "textval" IS NOT NULL THEN
      SET "rv" = "rv"+"textval"
    END IF WHEN 'M' THEN
    SELECT FIRST "tagchoiceid" INTO "x" FROM "tagvalue" WHERE "taglocation" = "ptagloc" AND "id" = "parentid" AND "tagid" = "ptagid";
    IF "x" IS NULL THEN
      SET "x" = '_'
    END IF;
    SET "rv" = "rv"+"isnull"((SELECT "isnull"("longdescription","description") FROM "tagchoice" WHERE "taglocation" = "ptagloc" AND "tagid" = "ptagid" AND "tagchoiceid" = "x"),'')
  ELSE
    SET "i" = 0;
    FOR "fetch2for" AS "fetch2cursor" no scroll cursor FOR
      SELECT "isnull"("tagchoice"."longdescription","tagchoice"."description") AS "tagchdesc","tagvalue"."tagchoiceid","tagvalue"."value" AS "tgval","tagchoice"."tagchoiceparentid" AS "tcpid"
        FROM "tagvalue" KEY JOIN "tagchoice"
        WHERE "tagvalue"."taglocation" = "ptagloc" AND "tagvalue"."tagid" = "ptagid" AND "tagvalue"."id" = "parentid" ORDER BY "tagchoice"."sortorder" ASC FOR READ ONLY do
      IF "i" > 0 THEN
        SET "rv" = "rv"+', '
      END IF;
      CASE "xtagtype" 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 FIRST "isnull"("longdescription","description") INTO "xdesc" FROM "tagchoice" WHERE "taglocation" = "ptagloc" AND "tagid" = "ptagid" AND "value" = "tgval" AND "subchoice" = 1;
        IF "isnull"("xdesc",'') <> '' THEN
          SET "rv" = "rv"+"tagchdesc"+' '+"xdesc"
        END IF WHEN 'Q' THEN
        SELECT FIRST "isnull"("longdescription","description") INTO "xdesc" FROM "tagchoice" WHERE "taglocation" = "ptagloc" AND "tagid" = "ptagid" 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;
  RETURN("rv")
END /* DOC 
2017-06-09 PC Test and doc
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tag set longdescription='LongDescription' where taglocation='P' and tagid='~~~'; 
insert into tagchoice (taglocation,tagid,tagchoiceid,description,longdescription) on existing skip values ('P','~~~','A','testtag','LongTest');
delete from tagvalue where tagid='~~~' and id='TEST';
insert into tagvalue (taglocation,tagid,tagchoiceid,id,value) values ( 'P','~~~','A','TEST',99);
select NetLongQuestAnswer('P','~~~','TEST') as A from dummy;
expect A=08/04/1900;
*/