====== 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>
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>