Implementing Combined Queries

Objectives

After completing this lesson, you will be able to:

  • Implement combined queries

Combined Query Functions

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.

Methods of Combining Queries

  • Union combines results that appear in Query 1 or Query 2.
  • Intersection combines results that appear in Query 1 and Query 2.
  • Minus combines results that include everything in Query 1, except for what is also true in Query 2.

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.

Combined Query Scenarios

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.

Differences Between Standard Queries and Combined Queries

This table compares the different outcomes of two query types:

Query TypeObject TypeResult
Standard queryIncompatible objectsTwo blocks of unsynchronized data
Combined queryIncompatible objects (for example, Invoice Date and Reservation Date)A single column of data in a block (with a column header that reflects the object from the first query only)

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.

Advantages of Combined Queries

Reasons to Use Combined Queries

  • To make the construction of the query easier
  • To set the required query filters when using Boolean logic is not possible

Comparison of Query Filters and Combined Queries

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.

The Combined Query Technique

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.

Standard Query and Combined Query Results

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

QueryResult
Query 1All 211 products
Query 2Only the 8 products for which there has been a promotion
Combined queryOnly the 203 products for which there has been no promotion (Query 1 minus Query 2)

Key Aspects of Combined Queries

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

Key Aspects for Use of Combined Queries

  • Only combined queries that contain the same number of objects run successfully.
  • A combined query that returns data from more than one object in a column, for example, using the Union operator, must use objects of the same type (character, date, or number).
  • Only objects from the first query display in the block after the query is run.
  • Combined query objects display in the same column as the object from the first query.
  • Union is the default operator for a combined query, and it returns all values, excluding duplicates.
  • The Union All operator requires a custom formula or variable in Web Intelligence.

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.

Implement Combined Queries

Log in to track your progress & complete quizzes