Code Snippet List
Distinct Classes by Term
Suppose you want a list of classes an instructor taught in a given them but, if they teach multiple classes of the same course, you only want then listed once. You also need them in one field separated by commas.
select ssbsect_term_code, ssbsect_camp_code, XMLQUERY( ' string-join(distinct-values(//text()), ", ")' passing courses returning content ).getStringVal() as courses from (select ssbsect_term_code, ssbsect_camp_code, xmlagg (xmlelement ("e", SSBSECT_SUBJ_CODE||SSBSECT_CRSE_NUMB) ORDER BY SSBSECT_SUBJ_CODE,SSBSECT_CRSE_NUMB) as courses from ssbsect, sirasgn where ssbsect_term_code = sirasgn_term_code and ssbsect_crn = sirasgn_crn and sirasgn_pidm = <instructor_pidm> and ssbsect_term_code = <term_code> group by ssbsect_term_code, ssbsect_camp_code)
Filtering for Specific Groups
ProblemHow to filter a dataset that has a group of Y/N flags used as indicators for different groups of students.
We have a process in place to monitor academically selected groups of students. These are broken down into; ACT101, Special Needs(SN), Suspension/Reinstated(SR), KEYS and Developmental(DEVL). We need general reports that are limited only to students in a selected group and we also need contact reports that need to select students in a group but also make sure they are not in a group of higher precedence. This is to make sure a student is not contacted multiple times. As an example, if a student is in both the ACT101 and SN groups, the ACT counselors would be contacting them, not Special Needs counselors.
Since the flags are either Y or N, the following methodology was developed. Group the flags in one data field. This data field will contain a group of 5 character made up of either Y and N's. As an example, if a student is in the ACT101 and SR groups, the data field would be 'YNYNN'. I then needed to create a drop-down parameter box that holds the name of the group along with an index number. This is a Manual entries drop-down with the following values;
In order to accomplish the first reports, those that contain all students is a selected group I use the following field and condition on the index field;Field: substr(index_field,:parm_DD_GroupIndex_F0.index, 1)
In order to accomplish the second group of reports is a bit tricker;Field: substr(index_field,1,:parm_DD_GroupIndex_F0.index)