April 25, 2006 at 5:06 am
When I try to create a view with the following sql an error message appears as ......
Server: Msg 1033, Level 15, State 1, Procedure fsd, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
create view fsd as
select oc.oc_id, i.issue_id, i.issue_date,
(SELECT TOP 1 issue_date FROM issue WHERE oc_id = oc.oc_id AND issue_id > i.issue_id ORDER BY issue_id) as next_date
from oc
inner join issue i on oc.oc_id = i.oc_id
order by oc.oc_id,i.issue_id
Is there a solution to this ??
Regards
Phil
April 25, 2006 at 5:34 am
Haven't actually tested it when there also is a ordered subquery in the select list, but the problem is just what the message says... You need to say 'TOP'.
create view fsd as
select TOP 100 percent oc.oc_id, i.issue_id, i.issue_date,
(SELECT TOP 1 issue_date FROM issue WHERE oc_id = oc.oc_id AND issue_id > i.issue_id ORDER BY issue_id) as next_date
from oc
inner join issue i on oc.oc_id = i.oc_id
order by oc.oc_id,i.issue_id
/Kenneth
April 25, 2006 at 5:46 am
This is a follow up to this thread
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=275059
and the question was answered there - there is no reason to order the data while creating view, it can be unordered. ORDER BY will then be applied when selecting.
Philip, it is better to keep the questions together in one topic. If you start a new topic with additional question, people who read it don't know what it's all about and you have to explain again. from the beginning.
Vladan
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply