10.1 Combined queries defined
A combined query is a group of queries that work together to return a single result. You can combine queries in three relationships:
• union
• intersect
• minus
A UNION query takes all the data from both queries, eliminates duplicate rows, and builds a combined data set.
An INTERSECT query returns the data that is common to both queries.
A MINUS query returns the data in the first query that does not appear in the second.
Note:
You cannot combine BEx queries or OLAP queries.
Example: Union, intersect and minus queries
In this example you have two queries that return lists of countries as shown in the following table:
Values Query
US; UK; Germany; France Query 1
US; Spain Query 2
The different types of combined query return the following values:
Values Combination type
US; UK; Germany; France;Spain UNION
US;
INTERSECT
UK; Germany; France MINUS
Related Topics
•BEx queries defined
10.2 Uses of combined queries
Combined queries allow you to answer questions that are otherwise difficult or impossible to frame in standard query.
Example: Return a data set using a combined query
The Island Resorts Marketing sample universe contains the dimension Year, which returns guests who have already stayed in a resort, and Reservation Year, which returns guests who have reserved to stay in the future. Because of the structure of the database and universe, these objects are incompatible, which means that you cannot include them in the same block in a report.
What if you want to return a single list of years that includes those years where more than n guests stayed in a resort and those years where more than n guests reserved to stay in a resort? You can do this using a combined query, as follows:
Returns Query
Years where more than n guests stayed in a re-Query 1 sort
UNION
Years where more than n guests reserved to stay in a resort
Query 2
The union between these two queries returns the list of years that you want.
10.3 How combined queries are generated
If your database supports the type of combination in your query, combined queries work at the database level: they alter the query submitted to the database. They do so by generating query script containing UNION, INTERSECT and MINUS operators.
If your database does not support the type of combination in your query, the combination occurs after data retrieval. Multiple queries return data to the report and this data is then resolved into the same result generated by a database-level combined query.
10.4 To build a combined query
1. Create an initial query in the query panel.
2. Click the Add a Combined Query icon on the toolbar to display the Combined Queries panel beneath the list of query objects.
The Combined Queries panel shows the current query. You can change the query name by clicking the query in the panel, selecting Edit Name from the menu, then typing a new name in the Name box in the dialog box that appears.
3. Click Add a query to add another query. The second query appears in the Combined Queries pane and is:
• Combined with the original query in a UNION relationship.
• Named Combined Query #n.
4. To switch to a query, click it in the Combined Queries pane.
5. To delete a query select it in the Combined Queries pane and press the Delete key, or drag and drop the query to the universe outline.
6. To change the combination type, double-click on the operator. The operator moves through the sequence UNION, MINUS, INTERSECT.
7. Build each query within the combined query as you build any normal query.
8. Click Run Query.
10.5 Combined query structure
The queries within a combined query must return the same number of objects of the same data type and the objects must be in the same order. You cannot combine queries when the number of objects in the query results and the data types of those objects are not identical. For example, you cannot combine a query than returns Year with a query that returns Year and Revenue, and you cannot combine a query that returns Year with a query that returns Revenue.
You must also pay attention to the semantics of your combined queries. While it is possible to combine a query that returns Year with a query that returns Region if both dimensions are of the same data type, the result - a mixed list of years and regions - is unlikely to be meaningful. Typically, if your first query contains a Year dimension, your second query also contains a dimension that returns a list of years.
10.5.1 To return a list of years and reservation years based on the number of guests
You want to build a query that returns a list of years consisting of years where more than n guests stayed in a resort and years where more than n guests reserved to stay in a resort. The object you are filtering on must also be in the "Result Objects" pane.
1. Select the Island Resorts Marketing universe in the list of universes to open the Query Panel.
2. Drag the Year, Number of Guests, and Future Guests objects to the Result Objects pane.
3. Drag the Number of Guests object to the Query Filters pane and create a report filter that restricts Number of Guests to greater than n.
4. Click Combined Query.
The Combined Query pane appears in the bottom left of the Query panel with the two queries joined by UNION.
5. Click on the second query and remove the Year and Number of Guests objects.
6. Drag the Reservation Year object to the Result Objects pane.
7. Drag the Future Guests object to the Query Filters pane and create a report filter that restricts the future guests to greater than n.
8. Click Run Query.
The query returns the combined list of years and reservation years.
10.6 Combined query precedence
The order of execution in a combined query is crucial in determining the final result.
In the simplest form of combined query you combine two or more queries in a relationship as follows:
Query 1 Query 2 INTERSECTION
Query 3
In such a case, the first result to be calculated is the intersection between Combined Query n and Combined Query n + 1. The next result is the intersection between the first result and the result of Combined Query n + 2. Query execution continues in this way through all the queries in the relationship.
This gives the following result for the above example:
Data Query
US; UK; France; Germany Query 1
US; France; Finland Query 2
US; France INTERSECTION of 1 and 2
Data Query
US; Spain Query 3
US Final INTERSECTION
10.6.1 Nesting combined queries
By default, each time you add a combined query, it is combined at the initial combination level with existing queries. Each added query extends the list of combined queries. If you add Query 3 to Query 1 and Query 2, which are already combined in a UNION relationship, you get the following result:
Query 1
UNION Query 2
Query 3
You can also nest combined queries in complex, multi-level relationships to control the order of execution, as in the following example, which combines the result of Query 1 MINUS Query 2 in an INTERSECT relationship with Query 3.
Combined Query 1 MINUS
Combined Query 2 INTERSECT
Query 3
Query groups are processed from right to left and from top to bottom within each group.
(Higher-precedence groups, such as the MINUS group in the above example, appear indented to the right.) In the above example the first query to be calculated is the MINUS query. The result of the MINUS query is then combined in an INTERSECT query with Query 3.
Result Query
US; UK; Spain; Germany Query 1
Germany Query 2
US; UK; Spain Query 1 MINUS Query 2
Result Query
US; Spain; Finland Query 3
US; Spain (Query 1 MINUS Query 2)
INTERSECT Query 3
Note:
If your database directly supports the type of combined query you wish to execute, the script generated from the query contains the combination operators directly. In this case the order of precedence depends on the order of precedence defined in the database. See your database administrator for more details.
10.6.2 To set the order of precedence of combined queries
1. Build the queries you want to organize in the Combined Queries panel.
2. To nest a pair of queries, click on the "add new combined query node" button in order to create a new combined query node, then drag and drop a query on to the query with which you want to associate the nested pair.
The new combined query node is by default a UNION relationship.
3. Continue adding queries to the nested group by dragging and dropping them on to the space between any two queries already in the group.
4. To create further nested groups within an existing higher-precedence group, repeat the previous two steps.
5. Click the combination operators of all the groups in the query to change them as required.
Related Topics
•To build a combined query