>>I am creating a PV to create a report for a school app. The user specifies the parameters as follows:
>>
>>The user must specify the Term (?vTerm). The other parameters are optional: Student Name (?vStuFKey--one student only), Level (?vLevel--all classes for that level), Level + Section (?vSection--all activities for that level+section) OR Level + Section + Activity (?vActivity--one class only). I'm having trouble with the SQL WHERE clause. Let's take the simplest example: the user wants the report for the entire Term (all classes, all students). In that case I want all the other ?v-parameters ignored:
>>
>>1. Is there a wildcard character that I can use for the other ?v-parameters so that they will be ignored or be all-inclusive?
>>
>>2. If not, how can I write the SQL to scratch where I itch?
>>
>>TIA,
>>John
>
>John,
>
>what's your data model ? All data in one or more tables, relations between them ? I need more info ....

Vlad:

Thanks for kicking in here. Data is in 5 tables, most related by primary - foreign keys. All the SELECT [fields] and JOIN ON's are working fine. Here's my latest version of the SQL. Works great for one student:

SELECT Enroll.stufkey, Enroll.schedfkey, Enroll.term, Enroll.level, Enroll.section, Enroll.discipline, Student.name, Student.sponsor, Schedule.pkey, Schedule.teacher, Schedule.room, Schedule.time_begin, Schedule.time_end, TermDef.Desc AS TermDesc, ChargeDef.Desc AS ActDesc FROM ieli!enroll INNER JOIN ieli!termdef ON enroll.term = termdef.term INNER JOIN ieli!chargedef ON enroll.discipline = chargedef.activity INNER JOIN ieli!student ON enroll.StuFKey = Student.PKey INNER JOIN ieli!schedule ON Enroll.SchedFKey = Schedule.pkey WHERE Enroll.term = ?vTerm AND ( Enroll.stufkey = ?vStuFKey OR (Enroll.level = ?vLevel AND Enroll.section = ?vSection AND Enroll.discipline = ?vActivity) ) ORDER BY Enroll.level, Enroll.section, Enroll.discipline

Any thoughts? Thanks, again . . .

John