GQL Hints and Tips

This page contains a variety of hints concerning using GQL to query the Student Data Warehouse.

• Choosing Driving Object (Table)
• Counting
• Qualifying Code and Description Attributes
• Sorting
• Crosstab Reports
• Interrupting Queries That Take Too Long
• Setting Date Field Formats

Choosing Driving Object (Table)

The order in which you select items from objects is crucial for query speed. The important part of this process is picking what is known as the Driving Object (or Table). To select the Driving Object in a query

• Estimate the number of rows each table would return if queried separately.
• The Driving Object is the object in a query which returns the fewest rows.
• If only one object in a query is qualified, it is the Driving Object.
• Queries for data concerning a given student will have the Person object as the Driving Object, because you are specifying a single row by qualifying with a Student ID.
• Queries concerning a given course, based on CRN, will have the Registration or Schedule table as the Driving Object.

To run a query, follow these steps:

• Decide which object is the Driving Object.
• Select attributes from objects for your query, selecting attributes from the Driving Object last.
• If the order of the columns in the query results is not right, choose Reorder Columns from the Results menu.

Counting

When you want to count an attribute, use the COUNT function base on that attribute's unique identifier.

• To count per person (student, applicant, etc.) , use the Warehouse ID attribute.
• To count per course, use the CRN attribute.
• If you want a total count, including duplicates, use the COUNT function.
• If you don't want to count duplicates, use COUNT DISTINCT.
• To count the total number of course registrations, use COUNT based on the PIDM attribute in the Registration object.
• To count the number of students who have registered, use COUNT DISTINCT on the Warehouse ID attribute of the Registration object.

Qualifying Code and Description Attributes

When qualifying Code and Description attributes keep in mind:

• For single Code and Description attributes (those whose names end in Code or Desc), use the "=" qualifier to search for a particular code or description.
• For multiple codes or descriptions (those whose names end in Codes or Descs), use the "Contains" qualifier in order to find the rows you want.

Sorting

To sort a query based on an attribute you don't want included in the final query results:

• Select the attribute, clicking the sort box.
• Run the query.
• Click on the column header for the item you don't want to display and choose Hide Column from the Results menu.
• This will leave the query sorted, but will not display the unwanted attribute.
• This can be used to sort by Addr Zip Code while using the Addr CSZ attribute.

Crosstab Reports

When creating Crosstab reports, keep these things in mind:

• For a simple crosstab report, you need a query with three attributes selected.
• Group the first two attributes in your query by clicking the Group box to the right of the attribute's name.
• Sort these attributes if you wish by clicking the Sort box to the right of the attribute's name.
• The first column in your query results will be the vertical labels in your crosstab.
• The second column will be the horizontal labels in your crosstab.
• The third column will provide the values that fill the crosstab.
• For more complex crosstabs, each column in the query results adds another vertical or horizontal label, V H V H … The last column will still provide the values that fill the crosstab.
• After you have created the crosstab, you may change the vertical and horizontal labels by dragging them where you want them.

Interrupting Queries That Take Too Long

While learning about GQL, you may find yourself running queries that take forever. This usually means that you did not correctly select the Driving Object. To stop a query that is taking too long, follow these steps:

Macintosh Users

• Hold down the "flower" key and press the period (".") key to interrupt a running query.

Windows Users

• Windows users cannot interrupt a running query until it has starting returning results.
• You can set the Query Time before Prompt, by selecting Preferences from the Edit menu.
• In the Query Time (in Minutes) before Prompt input box, enter a number, and after that many minutes GQL will stop and ask you if you wish to continue your query.
• Once a query has started returning results, you may select Cancel Query from the Query menu to stop a query.

Setting Date Field Formats

When formatting dates, GQL uses the default format specified by your computer, or it overrides the default format if a user format is defined. To view or modify the date format for a date field in an object, click on the which is located to the left of the date field. For example, in the PERSON object, there are a couple of date fields: Birth Date and Visa Expiration Date. If you click on the to the left of the Visa Expiration Date the following window is displayed:

Clicking on the Edit button enables you to select a new Data format:

We suggest a format that employs a 4 digit Year (YYYY). If you have configured your computer to use such a date format, then selecting the Default item (circled) might be your best choice. However, if you want to select a different date format, click on the Date item and highlight the desired format.

This page was last updated on December 2, 1998.