Composite Query Considerations and Limitations

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.