CREATE View MyView AS
Select * from MyTable1
Union (Select * from MyTable2)
Where ...
Order by myField1,myField2

The Enterprise Manager view designer says it can't deal with that sort of thing, order or no order, Union queries are not supported, although it does process the query and, as far as I can determine, return the correct results.

The Query Analyzer on the other hand does not complain on syntax check, but refuses to compile and save the view with an error near Order By.

Selecting the text and pressing F5 will execute the query without any problem.

What the heck is going on here, and how does one get around this behavior?

TIA

Peter
TIA

Peter