Union Queries

Introduction

Note: For simplicity, this Guide assumes that you are making a report with two queries, but InfoView supports many more than just two.

Diagram showing how union queries work

Union queries stack the results of two queries on top of each other and present it automatically as a single, unified table. This can be an effective way to combine two facets of the same data or to stack the contents of two columns into one.

Because InfoView will be stacking your results tables, your two queries must have exactly the same number of columns (result objects) and these must be requested in a particular order. Because InfoView cannot necessarily match the rows to their correct columns by the field name, it completes the job by the order in which they are requested. If you have a different number of result objects, the queries wont work. If your objects are out of order, the results will be returned as a unified table, but the data from your second query will be in the wrong columns.

In cases where one query has fewer columns than the other (or where most of the columns are the same but not all), there are blank "filler" objects available that you can add to your report in place of the unmatched columns. Keep in mind that the object names in your Available Objects list will always reflect the objects selected in your first query, even if those are blank filler columns. All of the data is there, just under a different name.

Alternatives: Sometimes, the same business needs can be fulfilled by more complex filtering of a single query or by variables. See our Nesting Filters Guide and our Variables Guides for more information.

Video

Union Queries

Example: Building Space and Age

Buildings have multiple square footage counts that describe different areas in each building. Two of those square footage counts are Custodial Area square footage and Mechanical Area square footage. Ordinarily, they are presented side-by-side as two different fields. With union queries, these two fields can be stacked on top of each other instead, creating an easily summable column of data. Each building will have two rows now instead of one: one row will show the Custodial Area square footage and the next the Mechanical Area square footage.

Additionally, we'll want to know the age of the building. However, we don't want both rows to show this age because we might want to do some calculations with the age. If both rows showed the age of the building, each building age could easily be counted or summed twice.

Alternatives: The two square footage fields could also be added together via a variable. Each building would have only a single row and the variable would show Custodial Area + Mechanical Area square footage. For more information about this kind of variable please see our Variable Guides.

Building a Union Query

1. Start a new report.

2. Write your first query: select your result objects and define your filters. Make sure to note what order the result objects are in.

Note: If you know that your second query will have more columns than your first, consider adding filler objects now (see below for instructions).

Example: Building Space and Age

Screenshot of InfoView example query 1

3. Click on the "Add a Combined Query" button in the top row of the query panel.

Screenshot of InfoView showing the location of the combined query button

Note how a box pops in below your list of objects. Those are your two queries and you can toggle between them by clicking on the names.

Screenshot of InfoView showing additional combined query box in the query panel

Note also that Combined Query 1 and Combined query 2 both have exactly the same Result Objects selected by default, but that the filters are different.

Screenshot Query1:

Screenshot of InfoView showing the first combined query

Screenshot Query 2:

Screenshot of InfoView showing the second combined query

4. If applicable, make changes to the Result Objects selections in query two.

Example: Building Space and Age

Because we want Custodial Area and Mechanical Area to be in one column, we'll remove the one and replace it with the other. Because we do not want the building age to appear twice for each building, we'll remove that column and replace it with a filler object.

Note: Filler objects must be the same object type as their counterpart(s) in the other queries (e.g. Dimensions or Measures). Also, you have choices of what they contain, such as a single space " " or a dash "-" for dimensions. Read the descriptions for more information.

Screenshot of InfoView showing the completed second combined query

5. If applicable, make changes to the filters.

Note: If both of you queries are filtering on the same value via a prompt, you'll need to be very careful. Both queries need to have a filter with exact same prompt text (the value that appears in the text box) in order to unify into one single prompt, otherwise the field will present two separate prompts in the prompt window. 

Example: Building Space and Age

We want to pull data from the same year, so we'll add the same filter in to this query. Many union queries want different angles on the same data, so this area can be very different.

(See screenshot above)

6. Make sure your first and second queries have exactly the same number of columns and that they're in the right order.

Tip: I take a screenshot of my first query and compare it to my second so that I don't have to keep toggling back and forth.

If you've added more columns to your second query than exist in your first query, you will need to go back to your first query and add filler columns.

7. Run your report.

Example: Building Space and Age

Results of a non-union query:

Screenshot of example InfoView report not using a union query

Results of a union query:

Screenshot of example InfoView report using a union query

Tips and Tricks: Nesting Unions, Minus and Intersection Queries

Union, Minus and Intersection queries can be nested in the same way that filters can be nested with ANDs and ORs (see the Nesting Guide for more information.)

The button for nesting is in the top right corner of the combined query box:

Screenshot of InfoView combined query nesting button location