Creating Composite Query Definitions

Use the Specify Composite Query Manager page (PSCPQINFO) to specify composite query properties.

Navigation

Select Reporting Tools, Composite Query, Composite Query Manager.
Select the Add a New Value tab to add a new composite query.

Field or Control	Definition
Composite Query Name	This is a required field. Enter a unique name for the composite query. Note: The name of the composite query cannot be changed after you enter the name into the Specify Composite Query Properties page.
Type	Select whether the composite query is `Private` or `Public.` `Private`: Only the user ID that created the composite query can open, run, modify, or delete the composite query. `Public`: Any user with access to the base queries used by the composite query can run, modify, or delete the composite query.
Status	Select the status of the composite query. The available options are `In Progress,` `Active,` or `Inactive.` Note: Currently, composite queries do not apply the status logic based on the value set in this field; the status logic will be applied to the composite queries in future PeopleTools releases.
Description	This is an optional field. Enter a description for the composite query.
Next	Click to advance to the Select Base Query page.

Use the Select Base Query page (PSCPQSELECTQRY) to select one or two base queries for the composite query.

Note: Currently, only queries that do not have runtime prompts can be selected as base queries.

Navigation

Select Reporting Tools, Composite Query, Composite Query Manager.
Select the Add a New Value tab.
Enter the basic information in the Specify Composite Query Properties page and click the Next button.

Field or Control	Definition
Query Name	At least one query must be selected. The query names cannot be changed after you enter the values into this page and move to the next page.
Query Alias	Enter the query alias name. Note that the query aliases cannot: Include any space, special character, or database reserved word. Be changed after you enter the values into this page and move to the next page.
Next	This button is available after you select the name of the first base query. Click this button to advance to the Specify Query Joins page.

Field or Control

Definition

Query Name

At least one query must be selected.

The query names cannot be changed after you enter the values into this page and move to the next page.

Query Alias

Enter the query alias name. Note that the query aliases cannot:

Include any space, special character, or database reserved word.
Be changed after you enter the values into this page and move to the next page.

This button is available after you select the name of the first base query.

Click this button to advance to the Specify Query Joins page.

Use the Specify Query Joins page (PSCPQQRYJOINS) to define how selected queries (from the Select Base Query page) should be joined.

Navigation

Select Reporting Tools, Composite Query, Composite Query Manager.
Select the Add a New Value tab.
Enter the basic information in the Specify Composite Properties page and click the Next button.
Select two base queries in the Select Base Query page and click the Next button.

Field or Control	Definition
Joins	This section displays the query names and query alias of the selected queries. You can select a join type from the Join to Related Query drop-down list. The available values are `Inner Join` and `Left Outer Join.`
Query Join Details	Use this section to select the query fields and condition type used to join the selected queries. Query Field The first Query Field drop-down list includes the fields that you have selected as the output fields from the first base query. In the previous example, the first base query is QE_EMPLOYEE. Select a query field as a mapping field from the first Query Field drop-down list. Condition Type Select a value from the Condition Type drop-down list to define how the field values are compared. Only the subset of the condition types supported by PSQuery is supported by Composite Query. The available options are `equal to,` `greater than,` `less than,` `not greater than,not less than,` `equal to,` and `not equal to.` Query Field The second Query Field drop-down list includes the fields that you have selected as output fields from the second base query. In the previous example, the second base query is QE_JOBCODE. Select a query field as a mapping field from the second Query Field drop-down list.
Next	Click to advance to the Select Output Fields page.

Field or Control

Definition

Joins

This section displays the query names and query alias of the selected queries.

You can select a join type from the Join to Related Query drop-down list. The available values are Inner Join and Left Outer Join.

Query Join Details

Use this section to select the query fields and condition type used to join the selected queries.

Query Field
The first Query Field drop-down list includes the fields that you have selected as the output fields from the first base query. In the previous example, the first base query is QE_EMPLOYEE.

Select a query field as a mapping field from the first Query Field drop-down list.
Condition Type
Select a value from the Condition Type drop-down list to define how the field values are compared. Only the subset of the condition types supported by PSQuery is supported by Composite Query. The available options are equal to, greater than, less than, not greater than,not less than, equal to, and not equal to.
Query Field
The second Query Field drop-down list includes the fields that you have selected as output fields from the second base query. In the previous example, the second base query is QE_JOBCODE.

Select a query field as a mapping field from the second Query Field drop-down list.

Click to advance to the Select Output Fields page.

Use the Select Output Fields (PSCPQOUTPUTFILEDS) page to select the output fields for the composite query. This page displays all fields that are available from the base queries that you selected.

Navigation

Select Reporting Tools > Composite Query > Composite Query Manager.
Select the Add a New Value tab.
Enter the basic information in the Specify Composite Properties page and click the Next button.
Select two base queries in the Select Base Query page and click the Next button.
Specify query joint and click the Next button.

Field or Control	Definition
Select	Select the check box to the left of each field that you want to add to the composite query output field list.
Select All	Click this link to select all fields in the queries.
Clear All	Click this link to deselect all fields in the queries.

Note: If the output field of the base query is a drilling URL field or is mapped to the drilling URLs and you add this drilling URL field to the composite query output field list, then you must add all query fields that are used to build the drilling URLs to the composite query output field list.

If the base query includes the drilling URL fields, only the drilling URL fields that have the drilling URL type set to Query, Component, or External are listed in the Select Output Fields page.

Use the Composite Query Manager page (PSCPQMAIN) to view and maintain connected queries.

Navigation

Select Reporting Tools, Composite Query, Composite Query Manager.
Search for an existing query and click the corresponding Edit link.

Field or Control	Definition
Status	Select the status of the composite query. The available options are `In Progress,` `Active,` or `Inactive.` Note: Currently, composite queries do not apply the status logic based on the value set in this field, but the status logic will be applied to the composite queries in future PeopleTools releases.
Actions	The available options are: Add Prompt: Select this option to add additional prompts to the composite query. See Defining Composite Query Prompts. Add Query: Select this option to join or add additional base queries to the composite query. See Defining Joins in Composite Queries. Add Expression: Select this option to add additional expressions to the composite query. See Defining Expressions in Composite Queries. Add Drilling URL: Select this option to add drilling URLs to the composite query. See Defining Drilling URLs in Composite Queries.
Query Selected	This section lists the queries that you have selected using the Select Base Query page.
	Click the Expand icon to expand the list of the fields from the selected query.
	Click the Collapse icon to collapse the list of fields from the selected query.
	The Query Name icon indicates the name of the base query.
	The Query Field icon indicates the name of the fields in the base query.
	The Selected as Output Field icon indicates that this field is already selected as an output field for the composite query. In the previous example, the selected output fields are A.EMPLID, A.EFFDT, A.QE_EMPLOYEE_NAME, and A.QE_JOBCODE.
	Click the Add to Output Field List icon to add the corresponding field to the output field list for the composite query.
	Click the Menu icon to open the actions menu that is available for the selected query or field. If you click the Menu icon for a query, the available options are: Remove: Select this option to remove the corresponding query and all of its fields from the output field list. Select All Fields: Select this option to add all fields of the corresponding query to the output field list. Deselect All Fields: Select this option to remove all fields of the corresponding query from the output field list. If you click the Menu icon for a field, the available options are: Add Field: Select this option to add the corresponding field to the output field list. Add Filter: Select this option to create a filter for the composite query using the corresponding field. See Defining Filters in Composite Queries. Note: If the base query includes drilling URL fields, only the drilling URL fields that have the drilling URL type set to Query, Component, or External are listed in the Query Selected section. If the output field of the base query is a drilling URL field or is mapped to the drilling URLs and you add this drilling URL field to the composite query output field list, then you must add all query fields that are used to build the drilling URLs to the composite query output field list.
Joins	Click this button to display the Joins section and access the Query Join Details page, where you can view, define, and maintain query joins details for the composite query. See Defining Joins in Composite Queries.
Filters	Click this button to display the Filters section, where you can view and maintain filters criteria for the composite query. See Defining Filters in Composite Queries.
Having	Click this button to display the Having section, where you can view and maintain the having criteria for the composite query. See Defining HAVING Criteria in Composite Queries.
SQL	Click this button to display the SQL section, where you can view the SQL statement that is generated for the composite query. See Viewing SQL in Composite Queries.
	Move the mouse over the Options icon to display the action options, which are: Properties: Select this option to view the properties of the composite query. Run/Preview: Select this option to preview the composite query results. See Previewing Composite Query Results and Viewing Composite Query Properties.
Fields	The Fields section displays the output fields of the composite query when you first access the Composite Query Manager page. Use the Fields section to view, define, and maintain the output fields that are selected for the output column results. Initially, the Heading column displays the headings of the fields using the field headings from the base query. If the field is an expression created within the composite query, then this column displays the heading of the expression. You can use the Edit Field option from the Action icon to edit the headings and field properties. See Editing Field Properties. Use the Order By column to enter the sort order numbers for the output fields. Use the Aggregate column to apply aggregate functions to the output fields. See Applying Aggregate Functions in Composite Queries. Use the Action icon to add the output fields as the filters, edit the fields’ unique names and headings, edit the fields’ group order, move the output fields up or down, or remove the output fields from the output field list. See Editing Field Properties, Applying Group By Clause in Composite Queries, or Defining Filters in Composite Queries.

To create a composite query definition:

In PeopleSoft PIA, select Reporting Tools, Composite Query, Composite Query Manager.
The Composite Query Manager search page appears.
Click the Add a New Value link.
The Specify Composite Query Properties page appears.
Enter the composite query name, type, status, and description.

Note: The composite query name must be unique. Values in the Type and Status fields are required.
Click the Next button.

The Select Base Query page appears.
Select one or two base queries that are used for the composite query.

Note: Queries with runtime prompts cannot be used as base queries for a composite query.

Image: Select Base Query page, QE_EMPLOYEE and QE_JOBCODE

This example illustrates the fields and controls on the Select Base Query page. The first base query is QE_EMPLOYEE, with the alias set to EMPLOYEE. The second base query is QE_JOBCODE, with the alias set to JOB.
Click the Next button.

If you select two base queries using the Select Base Query page, the Specify Query Joins page appears, listing the selected queries.
Use the Specify Query Joins page to specify query join details.
1. Select a join type in the Join to Related Query drop-down list.
  The available options are Inner Join and Left Outer Join.
  
  In this example, select the Inner Join option.
2. In the Query Join Details section, note that the first Query Field drop-down list displays the fields that you have selected as output fields from the first base query. The second Query Field drop-down list displays the fields that you have selected as output fields from the second base query.
  In this example, the first base query is QE_EMPLOYEE and the second base query is QE_JOBCODE.
3. Select a query field as a mapping field from the first Query Field drop-down list.
  In this example, select A.QE_JOBCODE.
4. Select a condition type from the Condition Type drop-down list.
  Condition type defines how the field values are compared. The available options are equal to, greater than, less than, not greater than,not less than, equal to, and not equal to.
5. Select a query field as a mapping field from the second Query Field drop-down list.
  In this example, select A.QE_JOBCODE.
  
  Image: Specify Query Joins page listing the selected queries and the join details
  
  This example illustrates the fields and controls on the Specify Query Joins page listing the selected queries and the join details.
Click the Next button.

The Select Output Fields page appears listing all fields from the selected base queries.
Select the output fields for the composite query.

Image: Select Output Fields page, fields are selected

This example illustrates the selections on the Select Output Fields page. The A.EMPLID, A.EFFDT, and A.QE_EMPLOYEE_NAME fields are selected from the QE_EMPLOYEE query. The A.QE_JOBCODE field is selected from the QE_JOBCODE query.
Click the Next button.

The Query Composite Manager page appears and displays basic Composite Query definitions that you have defined for the composite query.
Click the Save button.

Image: Query Composite Manager page - EMPL_AND_JOB

This example illustrates the EMPL_AND_JOB composite query in the Query Composite Manager page. The Query Selected section displays the EMPLOYEE and JOB as the base queries. The Fields section lists the selected output fields. The A.EMPLID, A.EFFDT, and A.QE_EMPLOYEE_NAME fields are from the EMPLOYEE query. The A.QE_JOBCODE field is from the JOB query.

Creating Composite Query Definitions

Specifying Composite Query Properties

Selecting Base Queries

Specifying Query Joins

Selecting Output Fields

Using the Composite Query Manager Page

Creating a Composite Query Definition