Queries whose name begins with jicgc (Inter-Cultural Greek Council). Primary contact is Tenea Lowery lowerytj@jmu.edu Section last updated 3/15/2012. There was also a series of queries whose name began with JSA which, although I may not have authored, I am asked from time to time to modify. Paul Whatley whatlejp@jmu.edu is the user who calls about these queries. The JSA naming series seems to be used by more than “Greek Life”, and the SA appears to stand for Student Activities, although the naming convention did not originate with me. One modification made to this series was the changing of the in list operator. Users would call each year stating new Greek organization had been added, and would like for the new value to be added to the queries. To lessen maintenance, changed to using the not in list operator after the user of the queries shared which extra-curricular activities they did NOT want included in the results. 2/4/2013: After speaking with Paul Whatley’s replacement, Adam Lingberg (lindbeae@jmu.edu) decided to rename the JSA queries which belonged to this group. They are now named JFASL (Fraternity And Sorority Life).
Jicgc001 (TS): Greek Student's GPAs. cloned from JSA0001_GREEK_STDNT_GPA for Tenea Lowery. Returns academic data based upon a student being associated with one/ore extracurricular activity codes defined by the user.
Jicgc002 (TS): Greek Roster. cloned from JSA0005_GREEK_ROSTER for Tenea Lowery.
History
Queries whose name begins with jhist. Primary contact is Judith Hollowood hollowjb@jmu.edu Section last updated 3/15/2012.
Jhist001 (NTS): Enroll summ by major range. cloned from jusdh013a for Judith Hollowood. Needed specific rows so hard coded vs. having them run a query and ignore specific rows. User wanted to know which academic plans were consuming their 200 and 300 level history courses.
Jhist001a (NTS): Has hard coded reference to course range hist300-499.
Jhist001b (NTS): Breaks out all history courses to the four levels as consumed by academic plans, but this version built over academic degree data (graduated with the plan vs. plan they were in at time of course enrollment).
Honors College
Queries whose name begins with jhc. Primary contact is Tammy Steele steeletm@jmu.edu Section last updated 12/12/2017.
jhc001 (TS): Basic student group data. Prompts for a single student group value, returns academic data for all students active in the respective group.
jhc003 (TS): Final honors list. Prompts for an admit term. Originally written by Claire Rooney for Karen Allison to return student data for those students eligible to be reviewed for entrance into the honors program.
jhc004 (TS) Honor Orient Assignments. Prompts for an admit term and an admit type (Freshman or Transfer). Has the student groups which are for the honors program hard coded.
jhc005 (TS) Students eligible for honors. Prompts for a term and a GPA > the prompted value for students active in their career who are NOT in any of the honor program student groups.
jhc006 (TS) Honors study abroad. Needed to know which honors program students were studying abroad. New logic seems to be a studies abroad student is indicated by EITHER a study_agreement value OR enrolled in a course SA 001.
jhc007 (TS) Honor class enrollees in 499. Returns students active for the prompted term in a course whose course number is 499. Students must also be active in one of the student groups for honors program.
jhc007b (TS): Students NOT in honors course. Prompts for a term, returns students who are active in one of the honors student groups as of the beginning of the prompted for term who are NOT in at least one honors course for the term. Honors courses are defined as the course subject=HON or GHON or the 5th digit of the catalog number=H.
jhc007c (NTS): Honor aggregate course enroll. Prompts for a single term. Returns aggregate data for students active in one of the honors student groups as of the beginning of the prompted for term and the number of honors students enrolled in the given course.
jhc008 (TS) Honors decision status. Prompts for an admit term, returns UG Admissions decision status for true Freshman for the given admit term. Built over the custom record JAD_APP_HNR_DTA - JMU Honors Application Data.
jhc009 (TS) Honors housing. Prompts for an admit term, returns UG Admissions housing data for the given admit term. Built over the custom record JAD_APP_HNR_DTA - JMU Honors Application Data.
jhc010 (TS) Honors T shirts. Prompts for an admit term, returns aggregate counts of the number of each sized T shirt. Built over the custom record JAD_APP_HNR_DTA - JMU Honors Application Data.
jhc011 (TS) Honors scholarships. Prompts for an admit term, returns UG Admissions scholarship data for the given admit term. Built over the custom record JAD_APP_HNR_DTA - JMU Honors Application Data.
jhc013 (TS): Honor scholar/distinction stdn. Prompts for a term range. Returns a list of students conferred degrees for the given term range who were awarded a degree whose transcript text includes the words “Honors Scholar” or “Distinction”.
jhc014 (TS): Honors by college aggregate. Prompts for a term range. Returns aggregate data for those students active in one of the honors student groups as of the END of the prompted for term. For this query, College is defined as the college to which the students 1st major belongs.
jhc014a (TS): Honors by college details. Similar to jhc014, except details of the students are returned.
jhc015 (TS): Returns all active honors students as of the running of the query and whether or not they are an athlete.
jhc016 (TS): Honors requirements. Prompts for the current academic term. Includes hard coded logic to return all active honors college students who have applied to graduate and the status of the degree requirements specific to honors college students only. The requirement data was provided by Meghan Donnely of the registrars office.
jhc016a (TS): Honors; all RQ complete. Contains much of the same logic as jhc016, this version only returns active honors college students if they have completed all requirements associated with the requirements specific to the honors college.
jhc017 (TS): Applied to graduate. Returns active honors college students who have applied to graduate as of the term end date of the prompted for term.
Note about tracking honors college data as of 12/7/2017. Prior to this date, logic to determine IF a student was to be considered an honors student was based upon data in the record named stdnt_grps_hist, which is effective dated. Three different student groups were used 0103, 0104 and 0105. These were internally referenced as track1-3. As of 12/7/2017, with establishment of the honors college and, more specifically the honors MINOR the new logic for determining track for an Honors Student / Honors Minor is as follows:
Track 1 - Honors Minor (0000044000) only
Track 2 - Honors Minor (0000044000) and Student Group (0104)
Track 3 - Student Group only (0105)
Student group 0103 will no longer be used
This new logic required a rewrite of all queries in the JHCxxx series to meet the new logic. As once can see from the above logic, the data to constitute “an honors student” is now housed in two different records, both of which are effective dated. Plan data is in acad_plan whereas student group data is in stdnt_grps_hist. My first attempt to rewrite the jhcxxx series was to use the NEW style outer join logic. And of course we needed the ability to make the queries time sensitive to allow the user running the query to look back in time to terms older than 1178 when the new logic was required. This presented several challenges:
The functional areas keying the data (registrars office, and the newly named honors college) were NOT going to change historical data. This meant that anything prior to 1178 must use the old logic of the stdnt_grps_hist record.
Since the new logic was SQL syntactically an “OR” condition built over two different records, both of which are effective dated, the query writer MUST use outer join (OJ) logic. Of course there are two OJ styles commonly referred to as old and new. I began to use the new style OJ logic, and had the requirement on the first query rewrite to allow the user to obtain results for past terms via the term_tbl.term_end_dt field logic common in many other queries. This presented two issues. First, the new style OJ does not allow a FIELD to be used on the effective dated field. Second, when an attempt was made to force an OR condition between the two new style OJ records, I could not get rid of an unbalanced SQL error stating the parenthesis were unbalanced, when in fact, they were not. SO, use the new style OJ logic only when the query results do NOT need to be time sensitive.
Use the old style OJ logic when the query results do not need to be time sensitive. The old style OJ logic overcomes both issues documented above caused by using the new style OJ logic. I have saved private versions of queries in sprd named jhc01_newoj and jhc01_oldoj for cloning purposes.
