pears.GetQuestTextLong [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.GetQuestTextLong ======
<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"."GetQuestTextLong" IS 
{create function GetQuestTextLong 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(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,(if isnull(longdescription,'')='' then description else longdescription endif) as tagdesc,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+tagdesc+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 max(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 max(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 (if isnull(longdescription,'')='' then description else longdescription endif) from tagchoice where taglocation = tagloc and tagid = toptagid and tagchoiceid = x),'')
    else
      set i=0;
      for fetch2for as fetch2cursor no scroll cursor for
        select (if isnull(tagchoice.longdescription,'')='' then tagchoice.description else tagchoice.longdescription endif) as tagchdesc,tagvalue.tagchoiceid,tagvalue.value as tgval, tagchoice.tagchoiceparentid as tcpid
          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;
        case tagtype 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 max((if isnull(longdescription,'')='' then description else longdescription endif)) into xdesc from tagchoice where taglocation = tagloc and tagid = toptagid and value = tgval and subchoice = 1;
          if isnull(xdesc,'') <> '' then
            set rv=rv+tagchdesc+' '+xdesc
          end if when 'Q' then
          select max((if isnull(longdescription,'')='' then description else longdescription endif)) into xdesc from tagchoice where taglocation = tagloc and tagid = toptagid 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
    ;
    set rv=rv+newline end for;
  return(rv)
end
}
</code>