Student Faculty Information System

 

 

 

 

 

 

This is an overview of the nine pages in Query Manager – as shown above – along with a listing of elements on some of the pages.   For more information about these pages and how the elements are used, see the PS Query I and II – Introduction and PS Query III – Advanced Concepts.

Page Name	Usage and Elements
Records	Starting on the opening Query Manager page, select the records upon which to base the new query.  [To begin a new query, click on Create New Query and on the Records Page enter record information in the Search box under Find an Existing Record. To narrow down the choices, click on the Advanced Search link.  When you have located the record you want, click Add Record to open the Query Page.  When adding more records, you must also make appropriate selections on the Join pages (Select Join Type and Auto Join Criteria) before the Query page opens]
Query	The Query Page displays the fields in the records that are part of the query. This is where you add fields to the query content. You can also add additional records by performing joins. When you access this page, if you have selected the record for an effective-dated table, a page informs you that an effective date criteria has been automatically added for this record. Click OK to close the page. Query Page elements: AZ – Click this button to sort fields Hierarchy Join – Click this link to join parent-child records Delete button (minus sign) – Click this button to delete the displayed record Check All Fields – Click this button to select all of the fields in the record Uncheck All Fields – Click this button to clear all selected fields Fields column – Check these check boxes to select fields Key – Identifies the key fields in a record Join link – Identifies related record joins Use as Criteria (funnel with +) – Click to filter data from the query
Expressions	Expressions are calculations that PeopleSoft Query performs as part of a query. Use them when you must calculate a value that PeopleSoft Query does not provide by default (for example, to add the values from two fields together or to multiply a field value by a constant). You use expressions to display a field value differently from the way you store the value. You create a new expression in the Edit Expression Page by clicking on the Expressions tab. An expression can be used like a field. If you use an expression as a field, the expression is like any other field in a query. When you preview the query, the expression name appears as a column heading in the query. When selected for output, you can change its column heading or sort it. Normally, data that is the result of a calculation is produced when the query is run in real time. Edit Expression Page Elements: Expression Type – used to identify the data type of the value this expression will return.  Options are as follows: Character Long Character Number Signed Number Date Time DateTime Length – should contain the maximum length of the string. For Number and Signed Number Expression Types the Length field defines the total length of the number (integer portion + decimals portion). For example, if Length = 10 and Decimals = 3, then this means that the integer portion = 7 (Length – Decimals = Integer). Aggregate Function – should be toggled on if an expression includes an aggregate function, such as COUNT, SUM, or AVG. Add Prompt – click to add prompt properties for this expression Add Field – click to add a field to this expression.
Fields	View how fields are selected for output; view the properties of each field; and change headings, order-by numbers, and aggregate values. Fields Page Elements: Reorder/Sort button – displays the Edit Field Ordering page which enables you to change the column order and sort properties of the query Col (column) – displays the column order for each field listed. Record.Fieldname – shows the name of the record and name of the field you have selected, as stored in the database Format – shows the field type and length for each field (e.g., Char5 – the field is made up of 5 characters) Ord (order) – indicates if the field is selected for sorting.  The number “1” represents the highest order of sort.  The ordering defaults to ascending. XLAT (translate) – can be utilized to take advantage of translate values in your queries.  If a field has an associated translate value, the column will contain one of three values:  N, S, or L (N – None:   Translate code; S – Short:   Translate short name; L – Long:    Translate long name) Agg (aggregate) – enables you to utilize the following aggregate functions in summarizing your data:  Avg, Count, Max, Min, and Sum.  This topic is covered in more detail in the Intermediate Query class Heading Text  – displays the default heading as defined in the database Add Criteria – Click to add a row of criteria to the query.  Criteria are used to filter data in a query. Edit – Click to access the Edit Field Properties page and format the query output Delete – Click to delete the associated field from the query
Prompts	Adding a prompt lets you further refine a query when you run it. Creating a prompt is easiest through one of the other pages, Query, Fields or Criteria. The prompt can be observed, edited, and deleted from the Prompt Page. Important Note:  If you remove a prompt through another page (Query, Fields, Criteria), be sure that you delete it as well from the Prompt Page.  Otherwise, the query will not run properly.
Criteria	Use the Criteria page to view any existing criteria for your query, and if necessary, add or modify selection criteria for the query.  To selectively retrieve the data that you want, you define selection criteria.   Selection criteria refine your query by specifying conditions that the retrieved data must meet. You specify criteria properties on the Edit Criteria Properties Page by clicking on the Criteria tab.
Having	SQL does not support the use of aggregate functions in WHERE clauses. Therefore, after you have applied an aggregate function to a field, you cannot use that field in your selection criteria, which corresponds to a SQL WHERE clause. When you want to select rows based on the results of an aggregate function, Query Manager enables you to create HAVING criteria.
View SQL	View the underlying SQL code that Query Manager generates based on your query definition. You cannot modify SQL on this page.
Run	View the results of your query prior to saving in order to verify that your query yields the results that you need. Continue to adjust and preview your query as necessary.