CODEWITHSUNDEEP
sqlasp.netsql-server
Ankit Sharma
Ankit Sharma

Reputation: 1

SqlException (0x80131904): All queries combined using a UNION, INTERSECT

Query:

USE [IEC]
GO

ALTER VIEW [dbo].[V_AllOA]
AS
    SELECT ROW_NUMBER() OVER (ORDER BY OrderId) OAId,
           *
    FROM (
        SELECT *
        FROM BranchOrder
        WHERE Flag IN (1, 2)
            UNION
        SELECT *
        FROM BranchOrder12_13
        WHERE flag IN (1, 2)
            UNION
        SELECT *
        FROM BranchOrder11_12
        WHERE flag IN (1, 2)
    ) a

GO

enter image description here

Upvotes: 0

Views: 173

Answers (2)

sagi
sagi

Reputation: 40481

When using union, you must make sure all the columns, including their ORDER and TYPE are the same.

for example: table1

ID,  NAME   , LASTNAME
 1  'john'     'smith'

table2 -

ID , NAME , MIDDLENAME , LASTNAME
 4  'john'    'bing'      'smith'

On the example above, both table can't be union using select * since they have different number of columns.

In that case, you can specify the same columns that appears on both table in your select statement like this:

SELECT ID,NAME,LASTNAME from table1
UNION
SELECT ID,NAME,LASTNAME from table2

This is a correct query.

SELECT * FROM table1
UNION
SELECT * FROM TABLE2

This is not a correct query.

Upvotes: 1

makak
makak

Reputation: 397

Your tables BranchOrder, BranchOrder12_13, BranchOrder11_12 have different number of columns. They must all have exact number of columns and they must be in the same order - I mean if first column in first table is integer, the first column in other tables must be integer as well.

Upvotes: 2

Related Questions