Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
View with union and order by returns error
Message
From
02/02/2004 05:05:47
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00872222
Message ID:
00872877
Views:
16
Sergey,

As always, your help is to the point, and invaluable.

Thank you.

Peter

>Peter,
>
>The order by clause isn't supported in views. You can work around of this limitation by adding TOP 100 percent clause. Combining it wit derived table should give you desired result.
CREATE View MyView AS
>	SELECT TOP 100 PERCENT * FROM
>		(Select * from MyTable1
>		Where ...
>		Union
>		Select * from MyTable2
>		Where ...) dt1
>	Order by myField1,myField2
>
>
>>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
Peter Pirker


Whosoever shall not fall by the sword or by famine, shall fall by pestilence, so why bother shaving?

(Woody Allen)
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform