1. 

   TICCTech, Member

   I have created some reports and I am using a Parameter Query named "Student ID". It looks like this:
   select 0, 'All'
   union
   select student_id, student_id
   from DATABASENAME.students

   When I run it, I can select an individual student but I still get the information for ALL students
   I have tried to mimic the reports on your demo page but something is not right.
   The query looks like this
   CREATE PROCEDURE sp_DBR_allergies(studentid varchar(15))
   BEGIN

   SELECT
   a.student_id as 'Student ID',
   s.student_fname as 'Student',
   s.student_lname as 'Name',
   a.allergy as 'Allergy'
   FROM DATABASENAME.allergies a
   JOIN DATABASENAME.students s on a.student_id = s.student_id
   WHERE s.student_id
   IN (
   SELECT student_id
   FROM DATABASENAME.students
   WHERE a.student_id = studentid
   ) OR studentid = 0;
   END

   I can modify the query an make it look like this:
   SELECT student_id
   FROM DATABASENAME.students
   WHERE a.student_id = studentid);
   END

   That makes the the report work for each individual student but if I select "All" I get a blank report.

   I am thinking that it may either be the JOIN or the declaration of studentid as char(15) since in the report query is says "OR studentid = 0"
   Because my Student IDs are alphanumeric, I could not duplicate your demo and use tinyint
2. 

   myDBR Team, Key Master

   The reason you are getting all students every time is beacause "studentid = 0" always evaluates to true, if the automatic string conversion to varchar results zero. Try to avoid mixing datatypes, as you might get unexpected results.

   What you can do is to change the parameter query to look like this (provided that no student with student_id 'all' exists):

   select 'all', 'All'
   
   union
   
   select student_id, student_id
   
   from DATABASENAME.students
   
   

   Then you can use it in your query and the query will not make any datatype conversions (you can remove the extra subquery):

   SELECT
   
     a.student_id as 'Student ID',
   
     s.student_fname as 'Student',
   
     s.student_lname as 'Name',
   
     a.allergy as 'Allergy'
   
   FROM DATABASENAME.allergies a
   
     JOIN DATABASENAME.students s on a.student_id = s.student_id
   
   WHERE (s.student_id = studentid OR studentid = 'all');
   
   

   --
   myDBR Team

Reply

You must log in to post.