Previewing Composite Query Results and Viewing Composite Query Properties

Use the composite query Preview page (PSCPQPREVIEW) to preview the composite query results. By default, the system display the composite query without pruning and the results are based on the Maximum Rows Fetched setting. You can change the pruning type and the Maximum Rows Fetched setting to display more or fewer result rows.

Navigation:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Create a new composite query or open an existing one.

  3. In the Composite Query Manager page, move your mouse over the Action menu icon (next to the SQL button) and select the Run/Preview option.

    The composite query Preview page appears, displaying the composite query result.

This example illustrates the fields and controls on the composite query Preview page. The Pruning Type is set to No Pruning.

 Composite Query Preview page

Term

Definition

Pruning Type

Based on the attributes that are selected in the result sets and the attribute dependency, Composite Query might be able to simplify the SQL statements that are generated for the underlying queries. The pruning process removes the tables in the FROM clause if the pruning rules allow. It also removes the columns in the SELECT clause, which corresponds to the view row attributes that users have not selected.

The available pruning types are:

  • No Pruning

    Selecting this option enables the system to produce a full SQL statement without any pruning.

    By default, the Pruning Type is set to No Pruning.

  • Standard Pruning

    Selecting this option enables the system to prune only a field or a table when the pruning process does not change the result set. The result sets of the SQL statements that are generated by the Standard Pruning and No Pruning options are identical. The field is not pruned if it is used in the query, such as the JOIN criteria in the FROM, WHERE, GROUP BY, or ORDER BY clauses.

    Note: In general, standard pruning does not prune much except the Cartesian Join, where a table does not have any relationship with other tables.

  • Prune to Select List

    Selecting this option enables the system to remove all join criteria and joined table from the SQL if the fields from that table is not selected. The system can prune a table from the query if no field in the table is selected for the composite query. This pruning option can change the result set because the table that is being pruned can have been used to filter the result set based on the JOIN it has against the other tables.

    You should select the Prune to Select List option when the result set is not altered. To accomplish that, you should ensure the join produces only extra columns and nothing more. The implication is that the relationship between the joined tables is many-to-one or one-to-one.

Note: The Standard Pruning and Prune to Select List options enable the system to prune a field from the SELECT clause if it is not used by the composite query.

The dependency settings of the base queries in the Query Manager – Dependency tab are also used to determine whether a field or a table can be pruned. See Defining Record and Field Dependencies.

Maximum Rows Fetched

Use to specify the maximum number of rows returned.

By default, the value in the Maximum Rows Fetched field is set to 100.

Preview

Click this button to refresh the results using the new settings in the composite query Preview page.

If the composite query has prompts, a prompt window appears after you click the Preview button.

Download

Click to select an option to download the composite query results to either an HTML file or an XML file.

Steps Used to Preview Composite Query Results

To preview composite query results:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Create a new composite query or open an existing one.

  3. In the Composite Query Manager page, move your mouse over the Option menu icon (next to the SQL button).

  4. Select the Run/Preview option to preview the composite query results.

    If runtime prompts exist, the prompt dialog appears.

  5. If needed, enter the valid prompt values and click the OK button.

    The composite query Preview page appears, listing the composite query results.

  6. Optionally, change the values in the Pruning Type section or the Maximum Row Fetched field and then click the Preview button to view the newly refreshed composite query results.

  7. Optionally, use the Download menu link to download the full results of the composite query to an HTML or a XML file.

Use the Properties page (PSCPQPROPERTIES) to view and edit the property settings of the composite query, including owner ID, comments, and distinct option.

Navigation:

  1. Select Reporting Tools, Composite Query, Composite Query Manager.

  2. Create a new composite query or open an existing one.

  3. In the Composite Query Manager page, move your mouse over the Action menu icon (next to the SQL button).

  4. Select the Properties option to view the property settings of the composite query.

    The Properties page appears, displaying the properties settings of the composite query.

This example illustrates the fields and controls on the Properties page. The Distinct option is selected to eliminate duplicate rows in the composite query result.

 Properties page