University of Oregon

Student DWHS Archive of GQL queries (may be helpful for Bi/Q also)

Audience
Faculty/Staff
Researcher
Student
GTF

CLASS ROSTER

Function: To duplicate the class roster provided by the registrars office.

ALL TERMS (2) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID-Function: Count Distinct
Confidentiality Flag

All Students Object

Qualify Term = x
Select Class Standing
All Majors

Registration Object DRIVING TABLE

Qualify Include in Enrollment = Y
Qualify & Select CRN = x
Select Grading Option
Registration Status Code


LIST OF MAJORS AND ADVISERS

Function: To count majors, to set up office advising files, to check adviser names.

ALL TERMS (1) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID
Confidentiality flag

Current Students Object DRIVING TABLE

Qualify All Majors contains | x |
Student Level Code = UG
Hours Registered > 0
Select Class Standing
Major1, Major2, Major3, Major4
Primary Adviser Name

Note:
For minors, switch majors to minors and drop adviser name
For email addresses, also select email address in the Person Object



DEGREE LISTS

Function: To verify awarded degrees.

ALL TERMS  MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID

Degree Object DRIVING TABLE

Qualify Degree Term = 199xxx
Degree Status = AW
Degree Level = UG
Qualify & Select
All Degree Majors contains | x |
Select Degree Code
Degree Department Honor Description
Degree Date
Confidentiality Flag

For all graduate degrees:

Same except Last Name = sort 3
Degree Date = sort 1
Degree Code = sort 2
Degree Level = GR
Degree Term qualify (equal to or greater than 199301)

No need to select All Majors or any Honors info



DEGREE LIST FOR COMMENCEMENT CEREMONIES

Function: To identify students eligible to participate in June ceremony, and to create list and labels. To be run spring term, after degree application deadline.

ALL TERMS (1) MODEL

Person Object

Select Last Name (sort 1)
LFM Name
Student ID

Address Object

Qualify Address code
Select Address Line 1
Address Line 2
Address Line 3
Address Line 4

Degrees Object DRIVING TABLE

Fall:
Qualify Degree status = AW
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x01
Winter:
Qualify Degree status = AW
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x02
Spring:
Qualify Degree status in AP,CP,AW
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x03
Qualifying Address Code: Use RD or PR address for fall and winter grads, MA for spring applican'ts.


SCHEDULE OF CLASSES

Function: For schedule planning. To supplement the Course Edit Report.

ALL TERMS MODEL

Schedule Object DRIVING TABLE

Qualify Dept Code
Term
Section Status Code = A
Select in order
Subj Code
Course Numb (sort 1)
Title
CRN
Spec Approval Code
Credit Hours Min
Credit Hours Max
UO Grading Mode Code
Major Grading Mode Code
Begin Time
End Time
Room Number
Building Code
Weekdays
Instructor Names
Fee Amounts
Enroll Max
Enroll Actual


ENROLLMENT STATISTICS

Function: For up to date enrollment statistics. Easier to print than SSASECQ.

ALL TERMS MODEL (1)

Schedule Object DRIVING TABLE

Qualify Dept Code
Term
Section Status Code = A
Select Cross List (sort 1)
Subj Code
Course Numb (sort 2)
Title
Instructor (pipes removed)
CRN
Spec Approval Code
Enroll Max
Enroll Actual
Seats Available
Waitlist Count

Results: Show as report.
Subtotal the cross list codes--"none" for all columns except "Enroll Actual"



COURSE SUMMARY

Function: To compare various characteristics (time, days, room, actual enrollment, fee amounts) for a course or courses over several terms.

ALL TERMS (2) MODEL

Schedule Object DRIVING TABLE

Qualify Department Code
Term = x
Select Term (sort 2)
Course Number (sort 1)
Title
Begin Time
End Time
Days
Room Number
Building Code
Instructor Name
Actual Enrollment
Fee Detail Descriptions
Fee Amounts


TEACHING SCHEDULE REPORT

Combining Results

Schedule object

Schedule object
 

Join Columns:
 

Query Results 2 Query Results 1

Cross List Code Cross List Code

 --reorder columns (1st un-order all)
--resize, rename and combine columns
--rename, title

  1. Create first query (Teaching Sched 1). Use the Schedule object. This query brings in information for (a) courses that are non cross-listed and for (b) the 400-level cross-listed courses.
       
  2. Select
    1. SUBJ CODE
      Course Number
      Title
      CRN
      Credit Hours Min
      Credit Hours Max
      UO Grading Mode Code
      Begin Time
      End Time
      Weekdays
      Building Codes
      Room Number
      Instructor Names
      Enroll Proj
      Section Status Code
      Cross List Code (Sort 1)
      Enroll Actual
  3. Qualify
     
    1. TERM = term prompt AND
      Credit Subject = subject code prompt AND
      ((Course number < 500 AND Cross List Code IS NOT NULL) OR (Cross List Code IS NULL))
  4. After building and testing, save query as Teaching Sched 1.
  5. Create second query (Teaching Sched 2). Again, use the Schedule object. This query brings in information unique to the 500-level cross-listed courses.
      Select:
      1. Course Number
        CRN
        Enroll Proj
        Cross List Code
        Enroll Actual
    1. Sort:
      1. Cross List Code
    2. Qualify
      1. TERM = term prompt AND
        Credit Subject = subject code prompt AND
        Course number >= 500 AND
        Cross List Code IS NOT NULL
    3. After building and testing, save query as Teaching Sched 2.
  6.  
  7. Run Teaching Sched 1 and then run Teaching Sched 2 by loading and submitting.

  8.  
  9. Choose Combine Results (Join Columns) from the Results menu. Join on Cross List Code.
      Left Results Set Right Results Set
  10.  
    1. Column to Join Column to Join
  11. "Include unmatched rows" selected.
  12. Sort results (choose Filter, Sort from the Results menu). Sort by Subject Number, CRN. Save the query (it will be a super query).
  13. Choose Page Setup from the File menu and choose landscape orientation. You might want to change the report default font (choose Preferences from the Edit Menu).
  14. Choose Show as Report (GQL Standard) from the Results menu. Edit report, saving report specs every so often.
Note: You can't edit super queries. Thats why we saved the first two simple queries so that if we needed to change the super query, we can at least deconstruct it by editing the simple queries and then recombine and save as a new super query.


UNOFFICIAL TRANSCRIPT

Select the All Terms Model.

Transcript Object
 

Select Transcript Term (sort)
Subj Code
Course Numb
Title
Credit Hours
Grade
Transfer Subj Code
Transfer Course Numb
Transfer Title
Institution Desc

Person Object

Select FML Name
Qualify Student ID (you may insert prompt, if desired)

Submit query.
 

  1. Choose "Show as Report" from under the "Results" pull-down menu.
  2. Choose "GQL Standard".
  3. Choose "Reorder Columns..." from the "Reports" pull-down menu.
  4. Highlight "FML Name" and click on the "First" button. Click OK button.
  5. Select the "FML Name" header in the report.
  6. Select "Suppress Duplicates" from the "Report" pull-down menu.
  7. Select "Column Settings" from the "Report" pull-down menu.
  8. Choose the "Band Column" and "Before" options.
  9. To narrow the column widths, first adjust the titles of most columns, then click on the column and then drag the right column border to the left to make the column narrower.
  10. You can also click on the side of the "Title" column to re-size it by dragging.
  11. Double click on the "Query Results..." header, then click "Edit Text...".
  12. Enter a new title, then click "OK" twice.


CALCULATING A DEPARTMENTAL GPA

BY BUILDING A COMPUTED ATTRIBUTE

In addition to the attributes offered by the Student Data Warehouse in each of its objects, you can add your own custom-built attributes based on the attributes already available. You can save these "computed attributes" so you can use them for future queries. When you create an attribute it will be available to you but not to other users.

In this example, let's say you want to create a computed attribute that will calculate a GPA based on selected courses (say, all undergraduate courses with a "PS" subject code). Your plan for calculating the GPA is to use this fraction:

  • the sum of all grade points for selected courses divided by
  • the sum of all GPA hours for selected courses

To build your computed attribute:

Sort by building the numerator (see our formula above). Select the Grade Points attribute from the scrolling Table/Attributes window by clicking on it once. Press the Insert Function button and select SUM from the list on the left. Press the Insert button. (We could type in the attribute and function instead of inserting them, but its much easier to just pick from a list and press insert).

The expression

(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS))

should now appear in the expression box. That's the numerator. If it doesn't look right, just delete everything in the Edit Calculation window and do step 4 over again.
 

The expression should now look like this:

(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS)/)
 

(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS) / SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GPA_HOURS))

If you've made a mistake, edit the expression in the Edit Calculation window until it looks exactly like the expression above.
 

  1. Go to the All Terms (1) model.
  2. Open the Transcript (1) object.
  3. Now you need to decide where you want your new attribute to appear on the attribute list. Once you've decided, pick the attribute immediately above where you what the new attribute to appear and using its function box select the "calculation..." function. (After you have finished defining your new attribute, it's name will appear directly beneath the attribute you have selected.)
  4. You should now see the Edit Calculation window. Notice the Expressions box. This is where you build and edit your computed attribute. Click once on the box to begin (you should get a blinking cursor in the box). You can build an expression by a combination of typing, instating attributes, inserting functions, and/or using the keypad buttons.
  5. Now you need to put in the division operator. In the expression box move the cursor between the last two parentheses and press the "/" button.
  6. Now for the denominator. Make sure the cursor is blinking right after the / you just inserted. Select the GPA Hours attribute from the scrolling Table/Attributes window by clicking on it once. Press the Insert Function button and select SUM from the list on the left. Press the Insert button.

  7.  
  8. Your new attribute has been defined. The expression should now look like this:
  9. Type in a name for your new attribute in the Name: field (e.g., "Grouped GPA"). Press the OK button and your new attribute will appear on the attribute list.
How do you use your new attribute? Let's say you want to produce a report of all current political science majors including their names, ids, and GPAs based just on their political science courses.

Here's how you do it:

Note that you grouped on ID because you wanted a separate GPA for each student. You also made sure that the course counted in the GPA and that only PS courses were used in the calculation. And you made sure that GQL wouldn't have to divide by zero. You qualified on Course Level because GPAs should always be calculated by level. And finally, you used the Current Student object to only bring back GPAs for currently enrolled political science majors.

By changing the selection, you can calculate a GPA on other groups of courses making this a very handy attribute! The above query took about 30 minutes to execute.

  1. Go to the All Terms (1) model.
  2. Select New Query from the Query menu.
  3. Open the Person (1) object.
  4. Select Student ID and click its group box.
  5. Select LFM Name.
  6. Close the Person (2) window and open the Transcripts (1) object.
  7. Select the new Grouped GPA attribute.
  8. Qualify Count in GPA Flag = "Y".
  9. Qualify SUBJECT CODE = "PS"
  10. Qualify GPA Hours > 0 (GPA Hours is in the denominator and we can't divide by zero).
  11. You want only UO courses to be counted in the GPA, so qualify Inst Transfer Flag = "I".
  12. Qualify Course Level = UG (just undergrad courses).
  13. Close the Transcript (1) window and open the Current Students object.
  14. Qualify Hours Registered > 0.
  15. Qualify All Majors contains |PS|.
  16. Qualify Level Code = "UG" (just undergrad students).
  17. Run the query.


SELECTING STOPOUTS USING SUBQUERIES

We'll define a "stopout" as an undergraduate student who attended last term but who is not attending this term and who did not graduate. In our example, our list will be limited to Anthropology majors.

You'll use one subquery to eliminate from our list students who have graduated and you'll use another to eliminate students who are attending this term.

In the first subquery, you need to identify every student who has had a degree awarded at the UO. Then you'll use a NOT IN qualifier in our main query.

To define the first subquery:

  1. Go to the All Terms (2) Model.
  2. Select New Query from the Query menu.
  3. Open the Degree (2) object.
  4. Select Warehouse ID.
  5. Qualify Inst Transfer Flag = I.
  6. Qualify Degree Status = AW.
  7. Qualify Level Code = UG.
  8. Select Save Query from the Query menu.
  9. Type a descriptive name for the query (like "UG Degrees Awarded") in the Query Name field.
  10. Click save.

In the second subquery, you need to identify students who are enrolled the current term. Then you'll use another NOT IN qualifier in your main query.

To define the second subquery:

  1. Select New Query from the Query menu.
  2. Open the All Students object.
  3. Select Warehouse ID.
  4. Qualify Hours Registered > 0.
  5. Qualify TERM = the current term (e.g., 199602).
  6. Qualify Level Code = UG.
  7. Select Save Query from the Query menu.
  8. Type a descriptive name for the query (like "Current UGs") in the Query Name field.
  9. Click save.

Now for the main query. In it you'll select the Anthro majors here last term, using the subqueries to exclude those with degrees and those here this term.

To define the main query:

  1. Select New Query from the Query menu.
  2. Open the Person (2) object.
  3. Select STUDENT ID and LFM Name.
  4. Close the Person (2) window and open the All Students object.
  5. Qualify TERM = last term (e.g., 199601).
  6. Qualify Student Level Code = "UG".
  7. Qualify Hours Registered > 0.
  8. Qualify All Majors contains |ANTH|.
  9. Now you need to use your first subquery to eliminate the students who have a degree. To do this first, qualify on WAREHOUSE ID and choose the NOT IN qualifier.
  10. Select Insert Subquery from the Edit menu.
  11. In the Saved Queries list, select the subquery "UG Degrees Awarded" that we created above. Press the Load button and the query name will appear in the qualification box.
  12. Next you need to use your second subquery to eliminate currently enrolled students. Qualify WAREHOUSE ID again and choose the NOT IN qualifier.
  13. Select Insert Subquery from the Edit menu.
  14. In the Saved Queries list, select the subquery "Current UGs" that you created above. Press the Load button and the query name will appear in the qualification box.
  15. Run the query.