Defining HAVING Criteria

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. You might use such criteria, for example, when you want a list of the departments whose minimum salary is greater than 100,000.00 USD.

In SQL, a HAVING clause is similar to a WHERE clause 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.

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.

Use the Having page (QRY_HAVING) to view the having criteria.

Image: Having page

This example illustrates the fields and controls on the Having page.

Use the Edit Having Criteria Properties (QRY_CRITERIA_SEC) page to add or edit the having criteria properties.

Image: Edit Having Criteria Properties page

This example illustrates the fields and controls on the Edit Having Criteria Properties page.