====== pears.GetQuestGroup ======
<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"."GetQuestGroup" IS 
{create function GetQuestGroup 

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

(in stagloc char(3),in stagid char(10),in sparentid char(20))
returns long varchar
begin
  declare rv long varchar;
  declare prevtag char(3);
  declare multidesc long varchar;
  declare comma char(2);
  declare staggroup char(10);
  declare QUESTGROUPMARKER char(1);
  set QUESTGROUPMARKER="char"(127);
  set rv='';
  set staggroup='%';
  set stagid=trim(isnull(stagid,''));
  if "left"(stagid,1) = '*' then
    set staggroup=trim("right"(stagid,length(stagid)-1));
    set stagid='%';
    if staggroup = '' then
      set staggroup='[^-]%'
    end if
  end if;
  set prevtag='';
  set multidesc='';
  for fetchfor as fetchcursor no scroll cursor for
    select tagchoice.tagchoiceparentid,tagvalue.tagid,tagvalue.tagchoiceid,tagvalue.value,tagchoice.description,tag.tagtype,(if trim(isnull(tag.units,'')) <> '' then ' '+tag.units else '' endif) as units,
      (if tag.tagtype = 'G' then
        (select first description from tagchoice as s where tagvalue.value = s.value and tagvalue.taglocation = s.taglocation and tagvalue.tagid = s.tagid and s.subchoice = 1)
       else   
       if tag.tagtype = 'Q' then 
         (select first description from tagchoice as s where   
                 tagvalue.taglocation = s.taglocation and tagvalue.tagid = s.tagid and  
                 s.tagchoiceid in (select tagchoiceparentid from tagchoice C where 
                  tagvalue.taglocation = C.taglocation and tagvalue.tagid = C.tagid and  C.tagchoiceid = tagvalue.tagchoiceid))
      else null
      endif endif) as subchoicedescription from tagvalue key join(tag,tagchoice) where tag.tagtype like '[LGSQ]' and
      tagvalue.taglocation = stagloc and tagvalue.id = sparentid and tagvalue.tagid like stagid and string(tag.displaygroup,'') like staggroup order by
      tag.sortorder asc,tag.tagid, tagchoice.sortorder asc for read only do
    if tagid <> prevtag then
      if multidesc <> '' then
        set rv=string(rv,QUESTGROUPMARKER,prevtag,'DESCRIP',QUESTGROUPMARKER,multidesc)
      end if;
      set rv=string(rv,QUESTGROUPMARKER,tagid,QUESTGROUPMARKER);
      set prevtag=tagid;
      set multidesc='';
      set comma=''
    end if;
    if tagtype<>'Q' then  
       set rv=string(rv,tagchoiceid,"char"(9),value,"char"(13),"char"(10)); 
       set multidesc=string(multidesc,comma,description);    
     else        
       set rv=string(rv,tagchoiceparentid,"char"(9),tagchoiceid,"char"(13),"char"(10));  
       set multidesc=string(multidesc,comma,subchoicedescription);   
    end if;  
    set comma=', ';
    case tagtype when 'G' then
      set multidesc=string(multidesc,' ',subchoicedescription) when 'Q' then
      set multidesc=string(multidesc,' ',description) when 'S' then
      set multidesc=string(multidesc,' ',value,units) when 'L' then
      if tagchoiceid = '*' then
        set multidesc=string(multidesc,' ',value)
      end if
    end case
  end for;
  if multidesc <> '' then
    set rv=string(rv,QUESTGROUPMARKER,prevtag,'DESCRIP',QUESTGROUPMARKER,multidesc)
  end if;
  return rv
end
}
</code>