Code Snippet List

• Distinct Classes by Term
• Filtering for Specific Groups

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

Problem

Description

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.

Solution

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;

In order to accomplish the second group of reports is a bit tricker;