This write-up contains explanations and procedures for performing advanced GQL operations. The steps outlined here apply to both Macintosh and Windows users.

These are advanced features, and some of them are complex. You can go through this list and try various features, or you can wait until you want to do something difficult, and see if there is a procedure here for doing it.

Contents

• Advanced Query Features
  • Qualifying with Values from Other Objects
  • Qualifying with Prompts
  • Qualifying with Subqueries
  • Qualifying with DB Names
  • Changing Column Order
  • Changing Sort Order
  • Computed Attributes
  • Group Qualification
  • Negating Qualifications
  • Combining Qualifications
• Manipulating Query Results
  • Resorting Query Results
  • Restricting Attribute Range
  • Combining Query Results
  • Exporting Query Results to Other Applications

Advanced Query Features

Qualifying with Values from Other Objects

It is possible to look up data in other objects while qualifying queries. This includes the validation tables, and the data found can be inserted automatically into the qualification. For example, you may want to qualify a query based on Course Level. You can start your query, then use this feature to look up the values for Course Level from the Level validation table.

1. Open an object to start a query by double-clicking on it.
2. Start a qualification by clicking the Qualify box next to an attribute (e.g. Course Level).
3. From the Edit menu select Insert Data Values.
4. Select the object you are interested in by clicking on it in the Table Names list (e.g. Level).
5. Select the attribute you are interested in by clicking on it in the Attribute Names list (e.g. Level Code).
6. Click the Load Data button.
7. The data will appear in the Data Values window.
8. To insert one of the values into the qualification, click on it and click the Insert button.

Qualifying with Prompts

A prompt is a feature which allows you to automatically qualify a query with different values each time you run it. The prompt will display a message such as "Enter Student ID:" and allow you to type in a Student ID. The query will then use the value you enter as a qualification.

1. Open an object to start a query by double-clicking on it.
2. Start a qualification by clicking the Qualify box next to an attribute.
3. Select Insert Prompt from the Edit menu.
4. Click the New button.
5. In the Prompt ID field give this prompt a name (e.g. "Student ID Prompt").
6. In the Prompt String field, enter the text which will be displayed to ask for a value (e.g. "Enter a Student ID:") and click OK.
7. Click on the name of the prompt you just created in the Prompt Ids list.
8. Click Insert Prompt.
9. The prompt name will appear in the Qualification field (e.g. "<<Student ID Prompt>>").
10. When you submit the query, you will prompted for a value.
11. You can reuse this prompt in other queries by selecting it in the Insert Prompt window.

Qualifying with Subqueries

Sometimes a value you need to qualify a query must be created using another query (e.g. finding all students with a GPA >= the average GPA). This value can be derived using a subquery (in this case, finding average GPA is the subquery).

1. Create the subquery, you don't need to run it (e.g. select Accum UO GPA in the Summary Object and select Average Distinct from the Function box).
2. Select Save Query from the Query menu.
3. Type a descriptive name for the query in the Query Name field.
4. Click Save.
5. Select New Query from the Query menu.
6. Create your query (e.g. select Student ID from Person).
7. Qualify an attribute by clicking the Qualify box (e.g. Accum UO GPA >=).
8. Select Insert Subquery from the Edit menu.
9. In the Saved Queries list, select the subquery that you created above.
10. The query name will appear in the qualification box.
11. Run the query.

Qualifying with DB Names

You may qualify an attribute by comparing it to the value of another attribute (e.g. to select students who have earned all of the UO hours that they have attempted).

1. Open an object to start a query by double-clicking on it.
2. Start a qualification by clicking the Qualify box next to an attribute.
3. Select Insert DB Name from the Edit menu.
4. Select the name of the data object from the Data Objects list. You must select the data object you are qualifying.
5. Select the attribute you want to use for qualification from the Attribute Names list.
6. Click Insert Name.
7. The attribute name will appear in the qualification box.
8. Run the query.

Changing Column Order

You can change the order of columns in a query in several ways.

• Before you submit a query, select Column Order from the Query menu.
• After you submit a query, select Reorder Columns from the Results menu.
• In a GQL Standard Report, select Reorder Columns from the Report menu.

After selecting one of these options, you will see the Column Order window.

• In the Ordered Columns list, click on a column.
• The First, Last, Up and Down buttons move that column to the indicated place.
• Highlighted columns can be moved to the Unordered Columns list by clicking the << Move << button.
• Columns in the Unordered Column list are randomly ordered by GQL.

Changing Sort Order

Before you execute a query, you can change the sort order, which determines which columns are sorted first, second, etc., and also whether columns are sorted ascending or descending.

• Before you submit a query, select Sort Order from the Query menu.
• In the Sort Order window, if the Sort Order list is empty, then nothing is being sorted.
• To sort on a Selected Column, select the column in the Selected Column list and click >>Move>> to move it to the Sort Order list.
• To remove a column from the Sort Order list, select it and click the <<Move<< button.
• To move a column in the Sort Order list, click on it, then click First, Last, Up or Down. These buttons move the column to the indicated place.
• When a column is highlighted in the Sort Order list, click Ascending to sort it in ascending order, or Descending to sort it in descending order.

Computed Attributes

It is possible to add computed attributes to your queries. A computed attribute is an attribute that is not part of the original Student Data Warehouse objects, but instead is derived by combining data in other attributes or using special functions to get new data.

For example, you could use a computed attribute to add SAT scores and display their sum in a query. There are many math, date, and character functions you can use to create computed attributes. This is a complex feature of GQL; if you decide to use it, refer to the GQL User Manual, page 107, Computed Attributes.

Group Qualification

You may want to qualify an attribute that is a group attribute, such as a count or a sum.

For example, you may want to select all courses which have fewer than 5 students enrolled. To count students enrolled in courses, you need to use COUNT DISTINCT in the Registration object.

But GQL does not allow you to qualify an attribute if it is a group attribute. So, follow these steps to qualify a group attribute:

1. Start a query, selecting at least one group attribute.
2. Select Group Qualification from the Query menu.
3. In the Columns list, select the group qualification you are interested in.
4. Click Move, which moves the qualification to the bottom window.
5. In the bottom window, type in the qualification you want to use, such as <5.
6. You may also use prompts and database names here.
7. When you submit the query, the group qualification will be used to select data.

Negating Qualifications

Negating a qualification means only returning data that do not meet the given qualification.

To return all data that do not contain a certain value, qualify for that value and then negate the qualification. For example, to return students who are not undergraduate students, qualify Level = UG, then negate the qualification.

1. Start a query, and qualify an attribute.
2. Above the attribute name in the qualification, click on the rectangular box.
3. The box will darken, indicating that you selected it.
4. Select Negate Clause from the Query menu.

Combining Qualifications

You may have as many qualifications in a query as you like.

Sometimes you may need to group (or combine) qualifications to get the results you want. If you need to do this, follow these steps:

1. Create at least 3 qualifications, if you have two or less, there is no need to group them.
2. Decide which qualifications you wish to group together - you can group 2 or more.
3. Above each of these qualifications, click the rectangular box while holding down the shift key.
4. The boxes you click will darken, indicating that you selected them.
5. Select Combine from the Query menu.
6. GQL will graphically combine the qualifications and use them when you submit the query.

Once you have combined qualifications, you may want to Uncombine them:

1. When you have combined qualifications, they will be next to each other, with a vertical line above the connective (and or or) leading to a rectangular box.
2. Click the single rectangular box above the connective (and or or).
3. Do not click the boxes above the individual qualifications.
4. Select Uncombine from the Query menu.

Manipulating Query Results

Resorting Query Results

After a query has been submitted and results returned, you may re-sort the values without having to run the query again. This may be useful for queries that take a long time to run.

1. Start with a Query Results window.
2. After the results are returned, select Filter Results from the Results menu.
3. From the Selected Columns list, move columns to the Sort Order list by clicking on them and clicking the >>Move>> button.
4. Columns will be sorted in the order they are listed in the Sort Order list, from top to bottom.
5. Select columns in the Sort Order list to reorder them, and remove them.

Restricting Attribute Range

After a query has been submitted and results returned, you may restrict the selection of any attributes in the query, possibly reducing the number of rows in the query results.

This may be useful to avoid re-submitting a query that takes a long time to return results.

1. Start with a Query Results window.
2. After the results are returned, select Filter Results from the Results menu.
3. From the Columns list, select a column by clicking on it.
4. Enter a Minimum and/or Maximum value in the fields provided.
5. If you want data that falls outside the given range, click Not in range.

Combining Query Results

You may run a query more than once with different qualifications and then combine the results to get a single set of query results that you may display as a report, etc.

The query results that you combine must have the same attributes displayed.

1. Submit the query twice, using different qualifications so that you get different query results.
2. Do not delete either of the query results windows.
3. Select Combine Results from the Results menu.
4. Select Append Columns.
5. The names of the two results you want to combine will be showing in the Append Rows window.
6. Click OK.

Exporting Query Results to Other Applications

There are two basic methods for exporting query results to other applications, such as Microsoft Word or Microsoft Excel.

The first method is to copy and paste the results yourself.

The second method is to create a button in GQL that automatically exports a query to another application. This second method is a bit more complicated but worth it if you do this frequently.

Method I - Cut and Paste

1. Create and submit a query.
2. Select Select All from the Edit menu.
3. Select Copy from the Edit menu.
4. Start the application you wish to export to, such as Word or Excel.
5. Click on the place in that application where you want the query results to go.
6. Select Paste from the Edit menu.

Method II - Create a Button

1. Select New Query from the Query menu.
2. Select Create Button… from the Layout menu.
3. Click Edit Text to change the text that will appear on the button, then OK.
4. For Linkage select Query.
5. For Output select Application.
6. Click Results Options and make sure that the Record item in the Separator area is <CR><LF>.
7. Click Export Options.
8. From the Application list, choose the application you are interested in.
9. Click OK, then OK again.
10. You will now see your new button on the GQL Model. Use the hand to drag it to the place you want.
11. Select Edit Mode from the Layout menu.
12. You are now ready to use the button.
13. Create a query, but do not submit it.
14. Click the button you created, and the query will be exported to the application.

If you have problems with method II, don't panic. You can change the settings for the button which are usually the problem.

1. Select Edit Mode from the Layout menu.
2. Double click on the button you created.
3. Click on Export Options.
4. Check the settings there, especially the Execute Command Line.