|
|
Error Message:
Server: Msg 8157, Level 16, State 1,
Procedure View Name, Line 3
All the queries in a query expression containing a UNION
operator must have the same number of expressions in
their select lists.
Causes:
This error occurs when creating a view wherein the view uses the UNION or UNION
ALL operator and the number of columns specified in the SELECT clause on the
tables are not the same.
|
To illustrate, let’s say you have the following table definitions for the
company employees, past and present:
CREATE TABLE [dbo].[Employees] (
[EmployeeID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Position] VARCHAR(50)
)
CREATE TABLE [dbo].[OldEmployees] (
[EmployeeID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Position] VARCHAR(50),
[LastEmploymentDate] DATETIME
)
Then you decided to create a view on these two tables that will contain all
employees of the company:
CREATE VIEW [dbo].[AllEmployees]
AS
SELECT * FROM [dbo].[Employees]
UNION ALL
SELECT * FROM [dbo].[OldEmployees]
Since the [dbo].[OldEmployees] table has an extra column for the last employment
date of the employee, [LastEmploymentDate], the following error is encountered:
Server: Msg 8157, Level 16, State 1, Procedure AllEmployees, Line 3
All the queries in a query expression containing a UNION operator
must have the same number of expressions in their select lists.
Solution/Workaround:
When creating a view that merges or joins the results of two tables that do not
have the same number of columns, always specify the columns in the SELECT
clause instead of using the SELECT *. Also, use fillers for the missing
columns from each table. If the missing column is of VARCHAR data type,
you can use NULL or an empty string as filler for the missing column. If
the missing column is a numeric column, you can use either NULL or 0 for the
missing column.
To avoid the error generated from the above example, the script for the view
will look like the following:
CREATE VIEW [dbo].[AllEmployees]
AS
SELECT [EmployeeID], [FirstName], [LastName],
[Position], NULL AS [LastEmploymentDate]
FROM [dbo].[Employees]
UNION ALL
SELECT [EmployeeID], [FirstName], [LastName],
[Position], [LastEmploymentDate]
FROM [dbo].[OldEmployees]
Since the [dbo].[Employees] table does not have the [LastEmploymentDate] column,
a NULL value is returned for that column so that the number of columns in the
first table matches the number of columns in the second column.
|