====== pears.NetLongQuestAnswer ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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;
*/
}
</code>