SQL2000 views do not support the union SQL constructor [SQL | View | UNION]

 

Preface

Database view is a commonly used function. During the use process, you may occasionally need to UNION multiple tables. Here is a reference solution. Please refer to the text.

 

Environment

1. Microsoft SQL Server 2000 sp3

 

Body

1. First, the following error is reported when union is used in the View:

2. Solution: Table valued functions + views. Example:

SELECT LastName, FirstName, Title Employees
UNION
SELECT LastName, FirstName, Title Users

A). Create a table value function:

Create function dbo. FuncEmployeeAndUser ()
RETURNS TABLE
AS
RETURN (
SELECT LastName, FirstName, Title Employees
UNION
SELECT LastName, FirstName, Title Users
)

B). Create a view:

Create view EmployeeAndUser
SELECT *
FROM FuncEmployeeAndUser ()

 

End

Although a little troublesome, it is one of the solutions to such problems.