Implementing Combined Queries

Adding single and complex filters to a query allows you to restrict the amount of information that the query returns. This standard technique allows you to combine multiple filters in a single query, and you can design these combinations to make queries specific and limiting.

However, this technique only works with a single query. There are situations when you want to combine the results of two queries into a single data provider.

To accomplish this, build a combined query. All combined queries are built in the Query Panel, and can be built using a single universe. Each query must have the same number of objects and the same data types.

Combining Queries – Union

The figure Combining Queries – Union Method shows how the union function combines the results that appear in Query 1 or Query 2.

Union is the default operator for combined queries.

Use a union query to combine the data from two objects in a single column in a table. Union queries are especially useful for working with incompatible objects.

For example, if you build a query with two incompatible objects, Web Intelligence runs separate Structured Query Language (SQL) statements for each object and then returns the data in different blocks (tables). A union query forces Web Intelligence to return the data from both objects together, in one column.

Unions, when used to combine the results of two queries using the same objects, remove duplicate values, leaving a single instance of the duplicate in the report.

Combining Queries – Intersection

The figure Combining Queries – Intersection Method shows how the intersection function combines results that appear in Query 1 and Query 2.

Use an intersection query to obtain only the data that is common to two sets of results.

Like a union query, Web Intelligence considers each select statement separately and combines their results in the end.

Unlike a union query, the intersection query only returns those values that are in both queries. In this sense, it works in a similar way as using the AND operator when creating a regular query with multiple query filters.

Combining Queries – Minus

As shown in the figure Combining Queries – Minus Method, the minus function displays everything in Query 1, except for what is also true in Query 2.

Use a minus query to exclude the results of one query from the main query result (Query 1). For example, you can use a minus query to find out which customers bought product A but not product B.

You cannot obtain this data with standard query filters because the result sets must be obtained separately before being combined.

Like a union query, a minus query considers each query separately and combines their results in the end. When you build minus queries, pay attention to the order of the queries, because the results of Query 2 are always subtracted from the results of Query 1.

Here is a typical scenario for using a combined query: You want to find the dates on which your customers either made reservations or paid their invoices. This query requires two incompatible objects: Invoice Date and Reservation Date. While the data that the objects represent is identical (both are dates), the intent of the dates is incompatible, therefore, the objects are incompatible.

This table compares the different outcomes of two query types:

Combining queries is similar but not equivalent to Boolean logical operators that you use when combining conditions in a single query. The Intersection function is similar in effect to AND; the Union function is similar in effect to OR; and the Minus function is similar in effect to NOT.

However, the column of data retrieved by the combined query displays a header that reflects the date object from the first query only. In other words, using the reservations example, the query returns a column entitled Invoice Date, but the column actually contains both invoice dates and reservation dates. It might be necessary to reformat the column header.

Create two queries, one showing the total list of products’ SKU numbers and SKU descriptions, the second showing the list of SKU numbers, SKU desc, and products that have had a promotion (Promotion y/n). Then, apply a count on the SKU number column for each query. You see that while the first query produces a list of 211 records, the second produces a list of only 8 records.

This result occurs because the eFashion database only has information on product promotions where a promotion has actually occurred.

In this case, out of the 211 records available, there are only eight records where an entry identifies that a promotion has taken place. If you apply a query filter to display only those products where the Promotion y/n value is not equal to Yes, a message states that there is no data to return. The same message appears if you apply a query filter to display only those products where the Promotion y/n value is null.

Due to a relational database limitation, when products are listed within a Promotion column, products that have a Promotion y/n value show up in the report.

This is a common data integrity issue. Use a combined query to generate the results you need.

In the query filter scenario, you are unable to generate a table listing only the products for which there were no promotions, because the Promotion y/n object only contains data about the products for which there were promotions. However, you can generate the table you need by creating a combined query using the SKU number and SKU desc objects in Combined Query 1 and adding the Promotion (y/n) equal to y query filter to Combined Query 2.

This table compares the results generated by the two standard queries and the combined query:

When using combined queries, remember that you can change the default behavior of Web Intelligence so that the combined query returns only unique rows.

In a database, the same data may be repeated over many records. By default, Web Intelligence automatically returns all rows, even if they are repeated. This default behavior may cause an inaccurate count when combining queries, particularly when you use the Minus operator.

You can change the default behavior of Web Intelligence so that the combined query returns only unique rows.