Student Faculty Information System

 

 

 

DEFINING CRITERIA AND HAVING

Criteria: 

• Because your PeopleSoft database stores data in tables, you can identify every individual piece of data by saying what column (field) and row (record) it is in.
  • When you create a query, select the data that you want by specifying which columns and rows you want the system to retrieve.
  • If you run the query after selecting the fields, the system retrieves all the data in those columns; that is, it retrieves the data from every row in the table or tables. This might be much more data than you want or need.
• You select which rows of data you want by adding selection criteria to the query.
  • If you were to look at the SQL underlying the query, you would see that the WHERE clause specifies the selection criteria that field values must meet for the rows that contain the values to be included in the query results.
  • The selection criteria serve as a test that the system applies to each row of data in the tables that you are querying.
  • If the row passes the test, the system retrieves it; if the row does not pass, the system does not retrieve it.
• Criteria Properties in Query
  • Click for more details on Criteria Properties and Condition Types

 

Grouping Criteria

• Understanding how Query Manager processes criteria – in a certain order based on the way in which the system applies Boolean expressions – enables you to retrieve the results that you need.  Query Manager uses the following rules when processing criteria:
  • Criteria with enclosed parentheses are evaluated
  • Followed by NOT criteria
  • Followed by AND criteria
  • Followed by OR criteria
• PeopleSoft Query evaluates the order of operation and retrieves the requested data.
• When you have more than one criteria row, you can use the Group Criteria feature to control the order in which Query Manager applies the criteria row.  You enclose the criteria within parentheses to force the system to evaluate those criteria first.
• This document displays a step-by-step example of how to utilize Group Criteria.

 

---

---

Having:

• A HAVING clause is similar to a WHERE clause (Criteria), but used for rows of data that have been aggregated into a single row of output.
  • The system evaluates WHERE clauses by looking at the individual table rows before they are grouped by the aggregate function, and then it evaluates HAVING clauses after applying the function.
  • So if you want to check the value that is returned by the function, you must define a HAVING criterion.
  • 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.
• Essentially, use HAVING to select rows based on results of an aggregated function.
• When you click the Add Criteria icon from the Fields or Query pages for an aggregate field, new criteria is added to the Having page instead of the Criteria page.
  • Add selection criteria using the Having page in the same way that you add selection criteria using the Criteria page.
  • Keep in mind that PeopleSoft Query compares the result of applying the aggregate function to the comparison value.

Further information about working with Criteria and Having and illustrated examples can be

• Introduction to PS Query – Part II – Query Manager
• PS Query – Part III Advanced Concepts