Viewing SQL in Composite Queries

Use the Composite Query Manager page - SQL section to view the generated SQL statement for a composite query. You can also use this section to view the SQL statement that is generated with the pruning 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, click the SQL button.

    The Composite Query Manager page - SQL section appears, displaying the generated SQL statement for the composite query. By default, the system displays the SQL statement in the No Pruning mode.

Image: Composite Query Manager page - SQL section

This example illustrates the Composite Query Manager page - SQL section with the SQL Pruning drop-down list set to No Pruning.

Composite Query Manager page - SQL section

SQL Pruning

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. That is, 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 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 tables from the SQL if the fields from that table are not selected. That is, 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 where 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.