Ошибка all queries combined using a union intersect or except
Обновлено: 04.07.2024
Returns distinct rows by comparing the results of two queries.
EXCEPT returns distinct rows from the left input query that aren't output by the right input query.
INTERSECT returns distinct rows that are output by both the left and right input queries operator.
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
Arguments
<query_specification> | ( <query_expression> )
Is a query specification or query expression that returns data to be compared with the data from another query specification or query expression. The definitions of the columns that are part of an EXCEPT or INTERSECT operation don't have to be the same. But, they must be comparable through implicit conversion. When data types differ, the rules for data type precedence determine the data type that is run for comparison.
The result is based on the same rules for combining expressions when the types are the same but differ in precision, scale, or length. For more information, see Precision, Scale, and Length (Transact-SQL).
The query specification or expression can't return xml, text, ntext, image, or nonbinary CLR user-defined type columns because these data types aren't comparable.
EXCEPT
Returns any distinct values from the query left of the EXCEPT operator. Those values return as long the right query doesn't return those values as well.
INTERSECT
Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.
Resolution
Cumulative updated information
For BizTalk Server 2009, the hotfix that resolves this issue is included in Cumulative Update 1 for BizTalk Server 2009.
For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2429050 Cumulative update package 1 for BizTalk Server 2009
Hotfix information
A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.
If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.
Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=supportNote The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.
Prerequisites
You must have Microsoft BizTalk Server 2006 R2 installed to apply this hotfix.
Restart requirement
You do not have to restart the computer after you apply this hotfix.
Hotfix replacement information
This hotfix does not replace any other hotfixes.
File information
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.
Guidelines for Creating and Using Common Table Expressions
The following guidelines apply to nonrecursive common table expressions. For guidelines that apply to recursive common table expressions, see Guidelines for Defining and Using Recursive Common Table Expressions that follows.
A CTE must be followed by a single SELECT , INSERT , UPDATE , or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL , UNION , INTERSECT , or EXCEPT .
A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
The following clauses cannot be used in the CTE_query_definition:
ORDER BY (except when a TOP clause is specified)
OPTION clause with query hints
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
A query referencing a CTE can be used to define a cursor.
Tables on remote servers can be referenced in the CTE.
When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
The following examples show how to use the INTERSECT and EXCEPT operators. The first query returns all values from the FactInternetSales table for comparison to the results with INTERSECT and EXCEPT .
The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.
The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.
Could someone help me with this exception? I don't understand what it means or how to fix it. It is an SqlException with the following message:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
I get it when running a query in pseudo code looking like this:
If I remove the a.Union(b) parts, it runs without the exception. So I know the error is there. But why do I get it? And how can I fix it? Am I doing something too crazy here? Have I misunderstood how to use the Union thing?
Basically what I have is some entities which have a foreign key to some other entity. And I need to get all the entities which either have that foreign key set to null or where that foreign entity fulfills some requirements.
158k 24 24 gold badges 232 232 silver badges 390 390 bronze badges 141k 162 162 gold badges 430 430 silver badges 593 593 bronze badges Re question; Yes and yes. Is that all sorted? I was AFK.Arguments
expression_name
Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.
column_name
Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
CTE_query_definition
Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE. For more information, see the Remarks section and CREATE VIEW (Transact-SQL).
If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
J. Using a common table expression within a CTAS statement
The following example creates a new table containing the total number of sales orders per year for each sales representative at Adventure Works Cycles.
K. Using a common table expression within a CETAS statement
The following example creates a new external table containing the total number of sales orders per year for each sales representative at Adventure Works Cycles.
L. Using multiple comma separated CTEs in a statement
The following example demonstrates including two CTEs in a single statement. The CTEs cannot be nested (no recursion).
Consider the following scenario in Microsoft BizTalk Server:
You have a Business Activity Monitoring (BAM) activity that is deployed and is collecting data.
The partitioning feature is enabled for the activity.
You define an additional milestone for the activity.
At a command prompt, you run the following command to update the activity:
bm.exe update-all –definitionfile:<BAM denifition.xml>
In this scenario, the update fails and you receive the following error message in the Command Prompt window:
Updating Activity… ERROR: The BAM deployment failed.
Encountered error while executing command on SQL Server “<SQL Server Name>”
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Remarks
The data types of comparable columns are returned by the queries left and right of the EXCEPT or INTERSECT operators. These data types can include character data types with different collations. When they do, the required comparison is run according to the rules of collation precedence. If you can't run this conversion, the SQL Server Database Engine returns an error.
When comparing column values for determining DISTINCT rows, two NULL values are considered equal.
EXCEPT and INTERSECT return the result set's column names that are the same as the column names that the query on the operator's left side returns.
Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
The nullability of any column in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column that is returned by the query on the operator's left side.
If EXCEPT or INTERSECT is used together with other operators in an expression, it's evaluated in the context of the following precedence:
Expressions in parentheses
The INTERSECT operator
EXCEPT and UNION evaluated from left to right based on their position in the expression
You can use EXCEPT or INTERSECT to compare more than two sets of queries. When you do, data type conversion is determined by comparing two queries at a time, and following the previously mentioned rules of expression evaluation.
EXCEPT and INTERSECT can't be used in distributed partitioned view definitions, query notifications.
EXCEPT and INTERSECT may be used in distributed queries, but are only executed on the local server and not pushed to the linked server. As such, using EXCEPT and INTERSECT in distributed queries may affect performance.
You can use fast forward-only and static cursors in the result set when they're used with an EXCEPT or INTERSECT operation. You can also use a keyset-driven or dynamic cursor together with an EXCEPT or INTERSECT operation. When you do, the cursor of the operation result set is converted to a static cursor.
When an EXCEPT operation is displayed by using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left anti semi join, and an INTERSECT operation appears as a left semi join.
8 Answers 8
Judging from the SQL error you listed you may be experiencing the same issue I was. Basically when Linq to SQL queries that use the Concat or Union extension method on two different queries it appears that there is a bug in Linq to SQL which optimizes each projection separately without regard to the fact that the projection must stay the same in order to accomplish the SQL Union.
If this happens to be your problem as well I've found a solution that is working for me as shown below.
14.2k 10 10 gold badges 97 97 silver badges 140 140 bronze badges Fixed my issue by adding zero to the ID which was being projected twice.Since this looks like a problem with the generated SQL, you should try to use either an SQL Profiler, or use this code for DebuggerWritter class to write the SQL to your Output Window in Visual Studio.
The SQL error is normally caused by the fields retrieved for UNION is not the same for the 2 queries. For example, if the first query might have 3 fields, but the second query has 4 fields, this error will occur. So, seeing the generated SQL will definitely help in this case.
4,546 6 6 gold badges 26 26 silver badges 45 45 bronze badgesCan you perhaps write it in a single query?
There are also ways of merging expressions ( OrElse ), but that isn't trivial.
Not sure where the error comes from, though!
edit: haven't tested it, but this should be logically equivalent to a UNION:
Not a good idea to mutate captured variables. Likely the cause of the error.
Here is another idea. The hint is in the error message.
Or play by adding another 'fake' Where till they do become equal :)
110k 16 16 gold badges 189 189 silver badges 293 293 bronze badges You are right in that the re-use introduces confusion, but note that none of those is captured. @svish: can you test what marc said? That is what I meant. I suspect it being IQueryable may cause a problem, just a hunch :)I would call data.GetCommand(query) and analyze the resulting DbCommand (especially the generated SQL string). That should give you a clue to what goes wrong.
There is no projection going on anywhere so I would expect both target lists to be the same.
You could try to reduce your query to a smaller one that still doesn't work. Start with query.Union(query) (this should at least work). Than add your Where calls one by one to see when it stops working.
It must be one of your Where calls that adds extra columns to your select list.
30.7k 12 12 gold badges 85 85 silver badges 128 128 bronze badges already looked at the sql, although I got it during debugging. Copied it and pasted it into sql manager in a new query. And I get the same error there. Problem is the query is quite large, and I am not very stable on reading sql.Are you by any chance passing in a value to the 'select' side in a variable, or are you returning the same field more than once? SP1 introduced a bug where it tries to 'optimize' out such things and that can cause union queries to break (due to the query parts 'optimizing' out different passed-in params).
If you post your actual query rather than pseudo code it makes it easier to identify if this is the case.
(And a workaround if this is the case is to materialize the individual parts first and then do a client-side (L2O) union).
11.9k 1 1 gold badge 37 37 silver badges 60 60 bronze badgesjpierson has the problem summarised correctly.
I also had the problem, this time caused by some literals in the select statement:
Dim results = (From t in TestDataContext.Table1 _
Where t.ID = WantedID _
Select t.name, SpecialField = 0, AnotherSpecialField = 0, t.Address).Union _
From t in TestDataContext.Table1 _
Where t.SecondID = WantedSecondID _
Select t.name, SpecialField = 1, AnotherSpecialField = 0, t.Address)
The first sub-query of "SpecialField = 0" and the "AnotherSpecialField = 0" were optimised, resulting in one field instead of two being used in the union, which will obviously fail.
I had to change the first query so that the SpecialField & AnotherSpecialField had different values, much like in the second sub-query.
Well I had an issue with this. Using Sql 08 i had two table functions that returned an int and a string in both cases. I created a complex object and used linq to attempt a UNION. Had an IEqualityComparer to do the comparision. All compiled fine, but crashed with a unsupported overload. Ok, i realised the problem discussed seemed to smack of defered execution. So i get the collections, and place ToList(), then do the UNION and it is all good. Not sure if this is helpful, but it works for me
Well, that I know. But what you are doing is to do the UNION locally. That may or may not be a good idea depending on your circumstances.Linked
Related
Hot Network Questions
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.
Examples
A. Creating a simple common table expression
The following example shows the total number of sales orders per year for each sales representative at Adventure Works Cycles.
B. Using a common table expression to limit counts and report averages
The following example shows the average number of sales orders for all years for the sales representatives.
C. Using multiple CTE definitions in a single query
The following example shows how to define more than one CTE in a single query. Notice that a comma is used to separate the CTE query definitions. The FORMAT function, used to display the monetary amounts in a currency format, is available in SQL Server 2012 and higher.
Here is a partial result set.
D. Using a recursive common table expression to display multiple levels of recursion
The following example shows the hierarchical list of managers and the employees who report to them. The example begins by creating and populating the dbo.MyEmployees table.
Using a recursive common table expression to display two levels of recursion
The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.
Using a recursive common table expression to display a hierarchical list
The following example adds the names of the manager and employees, and their respective titles. The hierarchy of managers and employees is additionally emphasized by indenting each level.
Using MAXRECURSION to cancel a statement
MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.
After the coding error is corrected, MAXRECURSION is no longer required. The following example shows the corrected code.
E. Using a common table expression to selectively step through a recursive relationship in a SELECT statement
The following example shows the hierarchy of product assemblies and components that are required to build the bicycle for ProductAssemblyID = 800 .
F. Using a recursive CTE in an UPDATE statement
The following example updates the PerAssemblyQty value for all parts that are used to build the product 'Road-550-W Yellow, 44' (ProductAssemblyID``800 ). The common table expression returns a hierarchical list of parts that are used to build ProductAssemblyID 800 and the components that are used to create those parts, and so on. Only the rows returned by the common table expression are modified.
H. Using multiple anchor and recursive members
The following example uses multiple anchor and recursive members to return all the ancestors of a specified person. A table is created and values inserted to establish the family genealogy returned by the recursive CTE.
I. Using analytical functions in a recursive CTE
The following example shows a pitfall that can occur when using an analytical or aggregate function in the recursive part of a CTE.
The following results are the expected results for the query.
The following results are the actual results for the query.
N returns 1 for each pass of the recursive part of the CTE because only the subset of data for that recursion level is passed to ROWNUMBER . For each of the iterations of the recursive part of the query, only one row is passed to ROWNUMBER .
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Features and Limitations of Common Table Expressions in Azure Synapse Analytics and Analytics Platform System (PDW)
The current implementation of CTEs in Azure Synapse Analytics and Analytics Platform System (PDW) have the following features and limitations:
A CTE can be specified in a SELECT statement.
A CTE can be specified in a CREATE VIEW statement.
A CTE can be specified in a CREATE TABLE AS SELECT (CTAS) statement.
A CTE can be specified in a CREATE REMOTE TABLE AS SELECT (CRTAS) statement.
A CTE can be specified in a CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.
A remote table can be referenced from a CTE.
An external table can be referenced from a CTE.
Multiple CTE query definitions can be defined in a CTE.
A CTE must be followed by a single SELECT statement. INSERT , UPDATE , DELETE , and MERGE statements are not supported.
A common table expression that includes references to itself (a recursive common table expression) is not supported.
Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE query definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
An ORDER BY clause cannot be used in the CTE_query_definition, except when a TOP clause is specified.
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
When used in statements prepared by sp_prepare , CTEs will behave the same way as other SELECT statements in PDW. However, if CTEs are used as part of CETAS prepared by sp_prepare , the behavior can defer from SQL Server and other PDW statements because of the way binding is implemented for sp_prepare . If SELECT that references CTE is using a wrong column that does not exist in CTE, the sp_prepare will pass without detecting the error, but the error will be thrown during sp_execute instead.
Remarks
Guidelines for Defining and Using Recursive Common Table Expressions
The following guidelines apply to defining a recursive common table expression:
The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.
Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
The number of columns in the anchor and recursive members must be the same.
The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
The FROM clause of a recursive member must refer only one time to the CTE expression_name.
The following items are not allowed in the CTE_query_definition of a recursive member:
PIVOT (When the database compatibility level is 110 or higher. See Breaking Changes to Database Engine Features in SQL Server 2016.)
LEFT , RIGHT , OUTER JOIN ( INNER JOIN is allowed)
A hint applied to a recursive reference to a CTE inside a CTE_query_definition.
The following guidelines apply to using a recursive common table expression:
All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.
An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT , UPDATE , DELETE , or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).
A view that contains a recursive common table expression cannot be used to update data.
Cursors may be defined on queries using CTEs. The CTE is the select_statement argument that defines the result set of the cursor. Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. If another cursor type is specified in a recursive CTE, the cursor type is converted to static.
Tables on remote servers may be referenced in the CTE. If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. This is one way to confirm proper recursion.
Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. For more information, see example K. Using analytical functions in a recursive CTE that follows.
Cause
This issue occurs because of a problem in the logic that handles BAM updates when partitioning is enabled.
Examples
The following examples show using the INTERSECT and EXCEPT operators. The first query returns all values from the Production.Product table for comparison to the results with INTERSECT and EXCEPT .
The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.
The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query.
The following query returns any distinct values from the query left of the EXCEPT operator that aren't also found on the right query. The tables are reversed from the previous example.
Читайте также: