The purpose of a Union Query in Cognos BI is to take data from two or more different queries and merge or union them into one query.  Sounds simple enough, right?  In order to accomplish this, there are a few rules that must be followed for the union to work correctly.

• The queries used to form the union query must have the same number of data items
• The data types must have compatible types and appear in the same order
• Numeric types that are compatible are integer, float, double, and decimal
• String types that are compatible are char, varchar, and longvarcahr
• Binary data types that are compatible are binary and varBinary
• Date types must match exactly

For instance, you have three queries that you want to report off of; one is actual numbers, one is forecast numbers and one is budget numbers.  Your queries also include Account number and location.  You can union these by ensuring that actual, budget and forecast are included in each query.  You simply create a data item and set it to zero for each of the data points that don’t exist in each query.  Your queries would look like this:

• Account, Location, Actual, Forecast (set to zero) and Budget (set to zero)
• Account, Location, Actual (set to zero), Forecast and Budget (set to zero)
• Account, Location, Actual (set to zero), Forecast (set to zero) and Budget

Union query the above three points together and you can now write a report that contains each data point!