Working with Unions

Unions enable you to get the results from two or more separate queries at the same time. You can create an union of multiple queries only 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.

In addition, if using drilling URLs, both queries in a union must have a drilling URL mapped to a field. The number of drilling URLs defined in the top level of the query must be the same.

Note: Values from the related language record are not retrieved for union queries.

You must understand SQL to ensure that you create logical union queries.

In Oracle databases, PeopleSoft LongCharacter fields use the CLOB datatype when their length definition exceeds 1333. Since CLOBS (and other binary datatypes) are not valid columns for all operators, all queries including columns with LongCharacter field length definition that exceeds 1333 should not include the UNION operator.

To create a union:

  1. Click the New Union link, which is available on the bottom of each Query Manager page except for the Run page.

    PeopleSoft Query automatically switches to the Records tab so that you can start defining the second query. Define that query in the same way that you define other queries.

    When you’re working on a union, each individual selection looks like an independent query, and for the most part they are independent. However, the first selection in the union—the one that you started before clicking the New Union link—has a special status. PeopleSoft Query determines the ordering of the rows and columns based on what you specify for the first selection. It also uses the column headings that you defined for the first selection.

  2. Navigate between the main query, subqueries, and unions using the Subquery/Union Navigation link.