Composite Query Overview

Composite Query enables you to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result.

You can create BI Publisher reports with composite query as a Data Source.

See Creating Data Sources, Registering Data Sources, Running BI Publisher PeopleSoft Query Reports.

Using Composite Query, you also have the option to:

• Select the SQL pruning option to remove tables and fields that are not needed base on users’ attribute selections.

• Set the ORDER BY and GROUP BY clauses.

• Set the WHERE clause as a filter over the result set of a composite query.

  Note: Fields in the WHERE clause cannot be pruned.

• Prune the base queries at runtime based on the selected output fields, join criteria, filter criteria, and ORDER BY clause.

• View pruned SQL statements for the composite query.

• Run the composite queries using Application Server.

• Reuse a composite query definition.

• Specify join criteria, field dependencies within a query, and field or table pruning options at query design time.

  See Defining Record and Field Dependencies.

• Set the result limit by selecting the attributes in the base queries.

Using Composite Query, note that:

• Composite Query should not be used for returning a large number of rows. The result set returned from a composite query should be 10,000 rows or less at runtime. The time-out limitation to run a composite query is the time-out limitation of the application servers.

  Make sure you construct the Join Criteria, Filter Criteria, and aggregations to reduce the amount of data returned by a composite query.

• Composite query does not change the SQL statements or the result sets of existing queries. If users select the Standard Pruning option or the Prune To Select option, the pruning process removes the selected fields or record from the query’s SQL statement if these fields or record are not needed. The Prune To Select pruning option can potentially change the result set.

• Relationships between different base queries are expressed by fields, not by expressions or prompts.

• A composite query can have a sibling at any level. However, no relationship exists between siblings other than that they have the same parent. A Cartesian Join between siblings is possible if the key to join parent and children has the one-to-many or many-to-many relationship. This relationship is different from connected queries, where siblings have the implication of performing the UNION clause to the result of the siblings.

• All base queries collect data from the same database. These base queries are joined through the join criteria, which appears in the FROM clause. Join Criteria are combined using the operator AND to avoid a Cartesian product. Join criteria between base queries does not support Right Outer Join or Outer Join.

• Users must specify the field dependencies to ensure correctness of SQL pruning.

  The pruning process may produce incorrect SQL statements if the dependencies are incomplete or incorrect.

  See Defining Record and Field Dependencies.

• All fields in base queries must have unique names. By default, the table alias and the field name, with a dot (.) in between, are used as the unique field name.

• If a query has the UNION construct, pruning is based on the main SELECT clause.

  If a field is removed from the main SELECT clause, it is removed from the UNION clause based on the pruning rules. If a field is selected in the main SELECT clause, it is automatically selected in the UNION clause.

• The Order By clause in the base queries is removed due to the limitation of Microsoft SQL server. If users need to sort the composite query results, they have to use Composite Query Manager to specify the Order By clause for the composite query.

• A composite query does not parse expressions.

  To support different database platforms, users must provide expression syntax as MetaSQL. Otherwise, the composite query will not work for other platforms.

• All related language and XLAT translations are performed at the query level.

Users must have the role of Composite Query Developer or permission list PTPT5100 to access the Composite Query component.