Open topic with navigation

Packaging Query Builder

The first step towards processing Prioritized Packages is building Queries. When complete, Award Methodologies and Budgets are defined using the Queries built here. Once those are created, the Prioritized Packages are processed.

Use the Packaging Query Builder to create queries based on the Academic and Financial Aid Years. Queries are stored here and applied to Prioritized Packages, Awards, Budgets, Financial Limits, and ISIR Batch Jobs. It's common to apply a query to packages, awards, and budgets concurrently. For example, a query built to determine in-state residents may be used in conjunction with packages, awards, and budgets. Or an ISIR Batch Job query may be used to identify students whose ISIR import will be assigned to a specific Financial Aid Year based on a particular Program, Campus, or GPA Grouping, for example.

Creating a query has three (3) basic steps that need to occur in the following order:

• Define the query
• Choose the Query Object(s)
• Build and Test the query

Note: SQL statements can be entered manually or modified in the Query Builder in addition to the method described below which will allow users without SQL knowledge to create queries.

Define the Query:

Clearly describe the query so that it is easily identifiable from the Student Criteria Selection grids accessible in each Prioritized Packaging window. Determine where the query should be used. For example, a query built to determine in-state residents may be used in conjunction with all three (3) Prioritized Packaging windows. If you want the query to be available to use to determine which budget (an in-state budget could be different from an out-state budget) to apply to qualifying students, the Budget check box must be selected, otherwise that query would not be visible if accessed from the Budgets window.   

1. From the CAMS Enterprise Home page click Financial Aid >Packaging >Prioritized >Package Criteria. The Packaging Query Builder window displays.
2. Select the appropriate Academic Year and Financial Aid Year, and then click Find to display any queries already associated with the selected Academic and Financial Aid year. You MUST click Find first to add a new record.
3. Check the box next to Use Advanced Query Editor to write your own SQL query using tables or views or to modify an existing query that was created either with the Advanced Query Editor or the normal Query Builder.

Note: Advanced SQL users can use the Advanced Query Builder to create and optimize their own queries using tables or views or edit an existing query. Note that once a query has been saved using the Advanced Query Editor, it cannot be edited with the normal Query Builder.

4. Right-click in the data grid to add a query, or double-click an existing query to modify it. The Query Builder detail window displays.
5. Provide a Query Description. This displays in the Student Criteria Selection grid accessible from each Prioritized Packaging window.
6. Click the appropriate check boxes to select the Package Query, Award Query,  Budget Query, Financial Limit, or ISIR Batch Job.  

Choose the Query Object(s):

Select one or more Query Objects to use. The Query Object is a View which may contain data from one or more database tables. Several Views are available to use with queries. Use the Add/Remove Object button to activate the views. Active views display in the color red in the drop-down list. To ensure that the selected View has the data you want to query, use the Show button to display a sample set of the data, which defaults to the 100 most recent data rows. The sample data set can be expanded by increasing the number of rows to show. The Query Object selected here determines which Query Fields are available (Step 3). The query may be built by selecting the first View, choosing the fields associated with the view, applying the conditions to the fields, and then repeating the process by selecting another Query Object, and so on. For example, to design a query to identify in-state residents, use the CAMS_StudentAddressList_View.

1. After defining the query, select the appropriate Query Object from the drop-down list. Although the default Query Object is the first View in the list, the CAMS_Student_View is always active and therefore always displays in the list in red.
2. Click Add/Remove Object to activate the selected Query Object. Click Yes on the confirmation message that displays. This is the only way to display the fields associated with that particular View in the Query Field. Therefore, even if a Query Object has been activated, and then another Query Object is selected, you will need to re-select the first Query Object and click Add/Remove Object to use those fields again. Once a Query Object has been activated, it continues to display in red in the list, even if it is not the current active View.
3. If desired, click Show to open a data grid that displays the most recently added 100 rows of data. Click Return to go back to the Query Builder detail window. The data set can be expanded by increasing the number in the Show Rows field.

Build the Query:

Use the Query Field, Operators, and Values to build the query. For example, a query designed to identify in-state residents may begin with the Query Object CAMS_StudentAddressList_View. Choose the Query Field to add the field to the query, the Operator field to apply conditions to it, and the Value field to indicate what those conditions should be. The list of available values is dynamically populated based on the Query Object selected. For example, selecting the CAMS_StudentAddressList_View populates the Value field with the columns available in that view. Those columns hold address related data. Operators are as follows:

*Note: The Like and Not Like Operators should only be used when attempting to query free form text fields (values that are not contained in a drop-down list). For example, if a city name has been entered several different ways, e.g., St. Louis, Saint Louis, St Louis, a wild card character such as the percent sign (%) can be used. Therefore, the query "S%" in the City Name field would return all city names beginning with the letter "S".

To include only active address types in the query, select Query Field Active Flag, Operator =, and Value Yes. The following step-by-step instructions will continue to build on this example. Actual steps may vary depending on the query you build.

1. After selecting the Query Objects (Ex. CAMS_StudentAddressList_View), select the Query Field (Ex. Active Flag).
2. Select the Operator (Ex. =)
3. Select the Value (Ex. Yes).
4. Click Append. This adds the statement to the query.
5. Click And to add the next condition to the query.
6. Repeat the first three steps, this time selecting Query Field Address Type, Operator =, Value Home.
7. Click Append to add this statement to the query.
8. Click And to add the next condition to the query.
9. Repeat the first three steps again, this time selecting Query Field State, Operator =, Value MO.
10. Click Append to add this statement to the query. This query is now complete and should read as follows:  

(CAMS_StudentAddressList_View.ActiveFlag = 'Yes') AND

(CAMS_StudentAddressList_View.AddressType = 'Home') AND

(CAMS_StudentAddressList_View.State = 'MO')

11. Click Test. The List ofStudents Returned From Query grid displays the first 100 unique values. Click Return to go back to the Query Builder detail window.
12. Click Add to save the query and return to the Stored Queries data grid, or Cancel to exit without saving.

Copy Queries

Because queries are attached to a specific Academic and Financial Aid Year, it is necessary to create them for each Academic and Financial Aid year. This can be done manually, or to save time, use the Copy button to copy all queries associated with one particular Academic and Financial Aid year to another Academic and Financial Aid year. Because queries may not be copied one at a time, make sure that all necessary queries are completed before copying to another Academic and Financial Aid Year.

1. On the Packaging Query Builder window, select the appropriate Academic and Financial Aid year, and then click Find to display any queries associated with the selected Academic and Financial Aid year.
2. Click Copy. The SQL Copy Criteria detail window opens with the Academic Year From and Financial Aid Year From displayed.
3. Select the Academic Year To and Financial Aid Year To.
4. Click Copy to copy the queries to the new Academic and Financial Aid Years, or click Cancel to exit without copying.

Note: Copy Queries should NOT be done here when using the Copy Package, Copy Methodology, or Copy Budgets functions. When using the Copy Package function, Package Criteria queries, Award Methodologies, and Budgets associated with the Package are copied automatically. The Copy Methodology and Copy Budgets functionalities will copy the associated queries

Note: Be sure that all queries for the Copy From Academic and Financial Aid years have been added prior to copying. If the Copy process occurs more than once, duplicate queries could be created in the Copy To Academic and Financial Aid years. If duplication occurs, highlight the unwanted query in the data grid and click the Delete button to remove. Queries already attached to packages, awards, and budgets cannot be deleted.   

Note:  When building queries utilizing the CAMS_FinancialStatus_View you must include @AcademicYear value from the AcademicYear Query Field and @FinancialAidYear value from the FinancialAidYear Query Field if you plan to copy the Query to another Academic Year and Financial Aid Year. This will allow CAMS to adjust the query to the Academic Year and Financial Aid Year to which it is copied.

Related Topics