Create view using UNION

I have 2 tables with exact structure:
Employee:
ID
FirstName
LastName
Position
ExEmployee:
ID
FirstName
LastName
Position
And the reference table
Position:
ID
Name
I want to create a view something like:
CREATE VIEW viewEMPLOEE_ALL
as
SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS LastName,
p.Name AS Position
FROM Employee e
LEFT JOIN Position p ON p.ID = e.Position
ORDER BY e.LastName, e.FirstName
UNION ALL
SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
LastName, p.Name AS Position
FROM Employee ex
LEFT JOIN Position p ON p.ID = ex.Position
ORDER BY ex.LastName, ex.FirstName
This is similar to my actual SQL as my original SQL is much much more table
join.
I got an error when I tried to run this SQL in Query Analyser:
"Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
The column prefix 'Employee' does not match with a table name or alias name
used in the query."Man Utd wrote:
> I have 2 tables with exact structure:
> Employee:
> ID
> FirstName
> LastName
> Position
>
> ExEmployee:
> ID
> FirstName
> LastName
> Position
>
> And the reference table
> Position:
> ID
> Name
> I want to create a view something like:
> CREATE VIEW viewEMPLOEE_ALL
> as
> SELECT e.ID AS EmployeeID, e.FirstName AS FirstName, e.LastName AS
> LastName, p.Name AS Position
> FROM Employee e
> LEFT JOIN Position p ON p.ID = e.Position
> ORDER BY e.LastName, e.FirstName
> UNION ALL
> SELECT ex.ID AS EmployeeID, ex.FirstName AS FirstName, ex.LastName AS
> LastName, p.Name AS Position
> FROM Employee ex
> LEFT JOIN Position p ON p.ID = ex.Position
> ORDER BY ex.LastName, ex.FirstName
> This is similar to my actual SQL as my original SQL is much much more
> table join.
> I got an error when I tried to run this SQL in Query Analyser:
> "Server: Msg 107, Level 16, State 3, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query.
> Server: Msg 107, Level 16, State 1, Procedure viewEMPLOEE_ALL, Line 5
> The column prefix 'Employee' does not match with a table name or
> alias name used in the query."
Remove the ORDER BY in the first SELECT and use the ordinal position for
the ORDER BY in the second only if absolutely necessary. You're better
off not using an ORDER BY at all since it just creates overhead. You can
always add the ORDER BY later on to any queries you write against the
view.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I removed the ORDER BY clause in each SELECT but still got the same error.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Man Utd wrote:
> Remove the ORDER BY in the first SELECT and use the ordinal position for
> the ORDER BY in the second only if absolutely necessary. You're better
> off not using an ORDER BY at all since it just creates overhead. You can
> always add the ORDER BY later on to any queries you write against the
> view.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hi,
Sorry, my careless mistake.
In my second SELECT statement I have a CASE comparing the "ExEmployee" table
field, but I used "Employee" instead.
Solved.
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:Om3fuTr2FHA.3912@.TK2MSFTNGP15.phx.gbl...
> I removed the ORDER BY clause in each SELECT but still got the same error.
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:#QNoEBr2FHA.744@.TK2MSFTNGP10.phx.gbl...
>