Using Connected Query Manager

Connected Query Manager enables a report developer to create a new connected query, edit an existing connected query, copy existing connected queries, and delete a connected query object, as well as run connected queries in three modes:

• Preview XML

• Run to window

• Schedule

The queries you defined in a connected query must exist as PeopleSoft queries in the database. In addition, you cannot specify a child query in a connected query that is defined in a union.

Connected Query Manager supports XML output format and user-defined output format (XFORM, which triggers a transformation XSLT). Connected Query structure consists of a set of PeopleSoft queries that are linked by related fields. No limits exist for the number of fields being linked between two queries. Parent and child pairs of queries can be linked together using sets of correlated fields, though those linkages are not required.

Each query being selected in a connected query structure has a set of image icons, which enables you to perform various actions:

Field or Control	Definition
	Click the Insert Child Query icon to insert a new query as a child of the selected query.
	Click the Insert Sibling Query icon to insert a new query as a sibling of the selected query.
	Click the Delete Query icon to delete a selected query.
	Click the Select Related Field icon to open a related field selection section.
	Click the Open Query icon to open a selected query in Query Manager in read-only mode.

Use the Connected Query Manager page (PSCONQRSBUILDER) to create and maintain connected queries.

1. Select Reporting Tools > Connected Query > Connected Query Manager.

2. Select the Add a New Value tab.

   Alternatively, click the Edit link.

Image: Connected Query Manager page

This example illustrates the fields and controls on the Connected Query Manager page. Definitions for the fields and controls appear following the example.

Use the Connected Query Transformation page (PSCONQRSXSLT) to view, add, or modify the list of transformation program XSLTs that are defined for the connected query.

Image: Connected Query Transformation page

This example illustrates the fields and controls on the Connected Query Transformation page.

See Extracting and Transforming Data from PeopleSoft Pure Internet Architecture.

Report developers use the Connected Query Properties page (PSCONQRSPROP) to associate a set of properties with a connected query definition, modify values of execution or output XML file based on user-selected properties, and ensure that user-selected properties (with PeopleTools projects) are compatible with PeopleTools 8.50 and later releases.

Image: Connected Query Properties page

This example illustrates the fields and controls on the Connected Query Properties page. You can find definitions for the fields and controls later on this page.

The PeopleCode-based Preview XML and C++ based traditional CQRuntime run each child query multiple times based on data rows that the parent query returns, so the Connected Query application runs the child SQL statement for every parent row with the different bind values. These two running options make the Connected Query application a universal solution that enables users to map multiple queries together without limiting the resulting object structure. Connected Query enables users to map fields between different queries, provide a number of nesting levels, and so on. If the OrderBy run-time optimization is not used, these two running options run plenty of SQL statements that could affect your system performance. The number of generated SQL statements depends on the number of nested levels and on the number of data rows that are returned by higher level queries. The following example describes the difference in executions with and without using of the OrderBy processing option.

Suppose that the connected query EMP_ADR consists of these two hierarchical queries that are linked by the field EMPL_ID:

EMP_DET (Level 0)
	 EMP_ADDRESS (Level 1)

Applying the PeopleCode-based Preview XML and C++ based CQRuntime options, each of the above queries performs a SELECT from a single table. Connected Query application generates the following SQL statements:

Level 0: SELECT EMPLID, NAME FROM EMP_DET
	 Level 1: SELECT EMPLID, ADDRESS FROM EMP_ADDRESS WHERE EMPLID=:1

In the previous SQL statements, Level 1 query (EMP_ADDRESS) runs for every row returned from the Level 0 query (EMP_DET) using a bind variable that is dynamically substituted with the EMPLID field value being returned from the Level 0 query.

If you apply the OrderBy Processing option, both queries are dynamically altered to include the ORDER BY clause, and the Connected Query application generates the following SQL statements:

Level 0: SELECT EMPLID, NAME FROM EMP_DET ORDER BY EMPLID
	 Level 1: SELECT EMPLID, ADDRESS FROM EMP_ADDRESS ORDER BY EMPLID

In the previous SQL statements, every SQL statement runs once. The data set being generated is created by the associated code that processes child query rows based on a concatenated set of related field values being returned from a parent query.

To use the OrderBy processing option, each query with the OrderBy processing turned on should meet the following requirements:

1. Parent and child queries must have one-to-many relationships.

   Every row of the related field values of the parent query can correspond to none, one, or many rows of child query data with the same set of related field values. Every row of the related field values of the child query can have only one row of parent data for the same set of related field values.

   For example, query DEPARTMENT is linked to query EMPLOYEE by the related field DEPTID. Each department has one or more employees, and each employee works in a single department, which is a one-to-many relationship. You can use the OrderBy processing option for this structure if DEPARTMENT is a Level 0 query and EMPLOYEE is a child query. However, if query EMPLOYEE is set at Level 0 and query DEPARTMENT is set as a child query, then you cannot use the OrderBy processing option because multiple employees work in a department, and that is a many-to-one relationship.

   In another example, a connected query has query JOB and query EMPLOYEE, and these queries are joined by the JOB_ID field. You can use the OrderBy processing option if:

   • JOB_ID is assigned to multiple employees and

   • Each employee has only one JOB_ID.

   However, you cannot use the OrderBy processing option if:

   • JOB_ID is assigned to multiple employees and

   • Each employee has multiple JOB_ID assignments.

2. All child queries (sibling queries) that have the same parent query must share the same set of related fields.

   For example, assume that a connected query has the following structure:

   DEP_EMP (Level 0)
   		EMPL (Level 1)
   		EMPL_DETAILS (Level 1)

   You can use the OrderBy processing option if:

   • DEP_EMP query is linked with EMPL query by the EMPL_ID field and

   • DEP_EMP query is linked with EMPL_DETAILS query by the EMPL_ID field.

   You cannot use the OrderBy processing option if:

   • DEP_EMP query is linked with the EMPL query by the EMPL_ID field and

   • DEP_EMP query is linked with the DEP_DETAILS query by the DEPTID field.

3. Child queries that constitute a single hierarchy should be joined by the same set of related fields as their parent and any other fields.

   For example, assume that a connected query has the following structure:

   DEP_EMP > EMPL > EMPL_ADDR

   You can use the OrderBy processing option if:

   • DEP_EMP query is linked with the EMPL query by the EMPL_ID field and

   • Query EMPL is linked to the EMPL_ADDR query by the related fields EMPL_ID and ADDRESS_ID.

4. Related fields that are identified as parent and child query relationships must follow the same field order in each query.

5. Parent queries on any level should not be restricted to the amount of data they return if the same restrictions do not exist for their child queries.

   Note: This rule is not required, but it is highly recommended.

   If the parent query limits the number of rows that are returned by any filter (for example, the WHERE clause) in the query and the child query does not use this filter; then Connected Query performance may be less efficient.

   If the parent query uses a prompt to limit the amount of data returned, you should add the same prompt to the child query to eliminate extra string processing and improve processing time.

Effective date operator is available in the Connected Query Manager page as a part of the field mapping process. You can select an effective date operator for any of the member queries if a child query has an EFFDT value selected as a related field.

Image: Connected Query Manager page and the Eff Date Operator list

This example shows the Connected Query Manager page and the Eff Date Operator (effective date operator) list.

Note that the effective date operator list:

• Is available only if a selected field has a format of EFFDT.

• Is available in the Connected Query Manager but not available in the Connected Query Quick Start.

• Has a default operator—an equal operator—like other fields.

• Need not be saved in the database before you preview the XML of a connected query.

Use the Connected Query Manager search page (PSCONQRSSRCH) to edit a connected query, copy an existing connected query, delete a connected query, preview results of a connected query, schedule a connected query to run immediately with results displayed in a separate window, and schedule a connected query to run at the preset time.

Image: Connected Query Manager search page

This example illustrates the fields and controls on the Connected Query Manager search page.

To edit existing connected queries:

1. Access the Connected Query Manager page by selecting Reporting Tools > Connected Query > Connected Query Manager.

2. Enter your search criteria and click the Search button.

3. In the Connected Query Manager search page, click the Edit link on the row with the connected query you want to edit.

4. Optionally, change the connected query structure, edit description fields, and change the connected query status.

Use the Copy a Connected Query page (PSCONQRSCOPY) to copy a connected query.

Image: Copy Connected Query page

This example illustrates the fields and controls on the Copy Connected Query page.

To copy a connected query:

1. Access the Copy Connected Query page by selecting Reporting Tools > Connected Query > Connected Query Manager.

2. Enter your selection criteria and click the Search button.

3. On the Connected Query Manager page, click the Copy link next to the connected query to copy.

To save a copy of the connected query:

1. Change the connected query name in the Target section with a new unique name.

2. Optionally, change ownership, descriptions, or the status of the query.

3. Click the OK button.

To delete a connected query:

1. Access the Connected Query Manager page by selecting Reporting Tools > Connected Query > Connected Query Manager.

2. Click the Delete link next to the connected query to delete.

   A confirmation message appears asking if you want to delete the existing connected query.

3. Click the OK button to continue and delete the connected query.

   Alternatively, click the Cancel button to cancel the deletion and return to the Connected Query Manager page.

Image: Example of the confirmation message

This is an example of the delete confirmation message.

Use the Definitional References to Connected Query page (PSCONQRS_DEFN_REF) to view all PeopleSoft definitional objects that use this particular connected query as the data source. The results show the definition ID, definition type, and definition sub-types if they are available.

These object types are included in the Definitional References to Connected Query page:

• BI Publisher (with the Report Definition or Data Source Definition subtypes)

• PeopleSoft Search Framework

Image: Definitional References to Connected Query page

This example illustrates the fields and controls on the Definitional References to Connected Query page.

When you open a PeopleSoft query using the Connected Query Manager, the Query Manager component appears in a read-only mode along with a message informing you that no changes are allowed to the query that is opened. The Query Manager that is being opened through the Connected Query Manager is fully functional. It enables you to run a query, view SQL that is being generated by query, view the query structure, view query prompts, view query criteria, and so on, but it doesn't allow you to save any changes for the opened query.

Image: Sample message received when you open a PeopleSoft query in the Connected Query Manager

This is an example of the message received when you open a PeopleSoft query in the Connected Query Manager.