Subqueries & Unions

Body

This article provides information on the use of subqueries and unions in PS queries.

Detailed Information

Subqueries

A subquery is literally a query within a query. The subquery runs first, then that information can be used as part of the main, or Parent, query criteria.
There are two general ways to use a subquery.

Option One: Test for existence or non-existence

In this situation, you have a subquery that selects rows based on a set of criteria. Then, in your Parent query, you have one criterion that specifies to select a row that exists in that subquery result set or does not exist in the subquery result set. Examples:

Parent Query	Operator	Subquery
Selects all prospects where…	…a record for that prospect exists in the subquery, which…	…selects all prospects who have submitted test scores.
Selects all prospects where…	…a record for that prospect exists in the subquery, which…	…selects all prospect who have a certain Academic Interest.
Selects all applicants where…	…a record for that applicant does not exist in the subquery, which…	…selects Immunization data for the applicant.

To test for existence or non-existence:

Add a new line to your criteria tab
Select a condition type of either "exists" or "does not exist". The page changes:
Click the "Define/Edit Subquery" link within the Expressions 2 box.
From here, build a query following the regular steps of adding a table or tables, joining the tables, and specifying criteria with these exceptions:
1. Select no fields for the field listing.
2. One of the tables used in the subquery must be joined to one of the tables in the parent query
The "Subquery/Union" Navigation link lets you move back and forth between the two queries.

showing the Select subquery or union to navigate to window.

Option Two: Compare a field to a list

In this situation, you have a subquery that again selects rows based on a set of criteria. In this case, however, the subquery returns a list of values for one field. Then, in the parent query, you have one criterion that compares a field or expression to the subquery to determine if there is (or is not) a matching value in the subquery list. Examples:

Parent Query	Operator	Subquery
Selects all prospects where EMPLID…	… is in the list of EMPLIDs returned by the subquery, which…	…selects the EMPLID of all prospects who have submitted test scores.
Selects all prospects where EMPLID…	… is in the list of EMPLIDs returned by the subquery, which…	…selects the EMPLID of all prospect who have a certain Academic Interest.
Selects all applicants where EMPLID…	… is not in the list of EMPLIDs returned by the subquery, which…	…selects the EMPLID of all applicants with Immunization data.

To compare a field to a list returned by a subquery:

Add a new line to your criteria tab
Select a field for Expression 1 to compare with the subquery
Select a condition type of either "in list" or "not in list".
Click the "Define/Edit Subquery" link within the "Expressions 2" box.
From here, build a query following the regular steps of adding a table or tables, joining the tables, and specifying criteria with these exceptions:
1. Select only one field or expression for the field listing – that field or expression must be comparable to the field used for comparison in the parent query
2. Be certain that no join is created between any tables in the subquery and the parent query

Unions

The query "Union" functions provides the capability of combining the result sets of two or more completely separate queries. You can only create a union of multiple queries when the queries have the following common elements:

The same number of selected fields
The same data types for all fields
The same display order for the columns

INFO: Translate values, long or short description, cannot be displayed in a union query. Only the code for the field can be selected for output display.

Suppose you want to see a list of both prospects and applicants, with their programs and plans, in the same list. Write and test one of the queries first. In this example, shown in the following four images, we extracted a list of prospects first.
showing the Records tab's chosen records section listing four records.

Showing the Fields tab which is showing the chosen fields.

showing the Criteria tab which showing three criteria being used.

Showing the results of the query.

Click the "New Union" link found at the bottom of most of the pages. You will be presented with the Records page to begin adding new tables to a query. Write the second query.

Notice how the record aliases start with higher letters. You won't have access to the records in the first query for compares or joins.
showing the Chosen records which shows the use of higher letters (E through H).

Showing the Fields tab which shows the fields that are available.

After selecting your fields, make sure they are in the same column order as the first query. Adjust by clicking the Column Order button.
showing the Edit Field Column Order window.

Even though it appears that you can set the sort order on any section of the query, it only takes effect when set on the top level of the query. Also, the column headings are taken from the top-level query.
Showing the Edit Field Sort Order window.

When you run the query now, it will include both prospect and applicant data.

Showing the query results from the query with a Union.

Environment

PeopleSoft Campus Solutions (CSPRD)
PeopleSoft Campus Solutions Reporting (CSRPT)
PeopleSoft Human Resources (HRPRD)
PeopleSoft Financials (FNPRD)