pears.GetQuestGroup

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
}