- Select the SQL pruning option to remove tables and fields that are not needed based 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.
- Prune the based 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.
- Set the result limit by selecting the attributes in the based queries.
Navigation: Main Menu -> Reporting Tools -> Composite Query -> Composite Query Manager
Add a new value
Select two of the existing queries, the alias names once selected here cannot be changed.
In the next step we specify the joins. In this example as we are querying the employee details, Emplid field is joined in both the queries.
Select the output fields from both the queries.
- 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. Need to make sure that 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
- Relationships between different based queries are expressed by fields, not by expressions or prompt.
- Users must specify the field dependencies to ensure correctness of SQL pruning.
- If a query has the UNION construct, pruning is based on the main SELECT clause
- A composite query does not parse expressions.
- 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.