1. 

   loydb, Member

   I've got a bunch of survey answers in a table. I've written a report that summarizes at the question level using db.hdr. The same report, running on the same tables, correctly summarizes two of the surveys' answers, and doesn't for the other two. Same report, same stored procedure, same tables. Is there something I can look for that is affecting this? Thanks.

   

   DROP PROCEDURE IF EXISTS sp_DBR_show_survey_detail
   
   $$
   
   CREATE PROCEDURE `sp_DBR_show_survey_detail`(inSurveyID int)
   
   BEGIN
   
   select filename as 'Survey Name' from research.surveys s where s.ID = inSurveyID; 
   
   select 'dbr.hdr', 'Question';
   
   select
   
   	q.ID as 'ID',
   
       q.LText as 'Question',
   
   	c.alias as 'Alias',
   
   	a.response as 'Response',
   
   	a.comment as 'Comment'
   
   from research.answers a
   
     join research.customers c on c.ID = a.customerID
   
     join research.questions q on a.questionID = q.ID
   
   where a.surveyID = inSurveyID;
   
   END
   
   $$
   
   
2. 

   myDBR Team, Key Master

   Loyd,
   order the result set using the header order:

   select 'dbr.hdr', 'ID', 'Question';
   
   select
   
     q.ID as 'ID',
   
     q.LText as 'Question',
   
     c.alias as 'Alias',
   
     a.response as 'Response',
   
     a.comment as 'Comment'
   
   from research.answers a
   
     join research.customers c on c.ID = a.customerID
   
     join research.questions q on a.questionID = q.ID
   
   where a.surveyID = inSurveyID
   
   order by q.ID, q.LText;

   --
   myDBR Team
3. 

   loydb, Member

   Thanks. I'm still not really clear on how it worked on two of the four surveys -- the only thing different was a '2' or a '5' versus a '1' or a '3' in the surveyID field in the answers table.

   Adding the order in worked -- but in this case I didn't really care about the order -- I just wanted them grouped under questions. I've got a couple of other reports that use dbr.hdr, without an explicit ordering, and they seem to work fine.
4. 

   myDBR Team, Key Master

   The dbr.hdr-command does not sort the rows. It changes the the group once new group is detected from the query result.

   The database may optimize the query differently and if you do not use ORDER BY in the query, the order of the rows may vary. So, if you use dbr.hdr, also use ORDER BY.

   --
   myDBR Team

Reply

You must log in to post.