Troubleshooting
Problem
Creating a Union query causes the following error message when Validating, clicking 'View Tabular Data', or executing the report. [Error Message - RSV-DR-0002 Unable to execute this request. QE-DEF-0182 The queries for the set operation are incompatible. Solution: The purpose of the SQL UNION command is to combine the results of two queries together. The two source queries are matched column for column to produce a single merged result. If the data types in the columns do not match then the merged result cannot be created in a consistent manner. For example, if the first query contains Date data in the first column and the second query contains Character] data in the first column then there is no way to merge these and preserve both the character and date data types in the first column of the result. There may be several causes and solutions for this
Symptom
- The data items in the two queries are not arranged in the same order. i.e. [Order date] is the first column in Query1 but the third column in Query2. Reordering the data items will allow the columns to be matched between the two queries in the union.
- The two queries have different numbers of data items. If the first query contains 5 data items and the second only has 3 then there are two data items that cannot be matching when performing the union. To resolve this either remove the unnecessary columns from one query or add "placeholder" data items of the appropriate data type to the second query. A null value of the appropriate type can be created using the cast() function in Report Studio. For example: cast(null, integer)
- The data types of the data items are different but must be used in the union. This scenario requires casting of the data items to a common data type. For example if one data item is decimal while the other is integer it is possible to cast the integer to a decimal type to make the two match. For example: cast([Integer data item], decimal)
Also note that some database software may enforce additional restrictions on the union operation. For example, some earlier databases required that the column aliases between the two source queries must use the same names. Refer to your database software documetation for details on the syntax requirements of the union, intersect, or except operation.
Related documents:
Resolving The Problem
The purpose of the SQL UNION command is to combine the results of two queries together. The two source queries are matched column for column to produce a single merged result. If the data types in the columns do not match then the merged result cannot be created in a consistent manner.
For example, if the first query contains Date data in the first column and the second query contains Character data in the first column then there is no way to merge these and preserve both the character and date data types in the first column of the result.
There may be several causes and solutions for this
Related Information
Historical Number
1008108
Was this topic helpful?
Document Information
Modified date:
28 November 2022
UID
swg21338754