====== 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>
create function "pears"."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" = any(select "tagchoiceparentid" from "tagchoice" as "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" asc,"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>