General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>>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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only