pears.NetLongQuestAnswer

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetLongQuestAnswer" IS 
{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;
*/
}