mardi 5 mai 2015

Order in for xml T-sql

select a.Hall, a.Title, 
STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))  
+ ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm)) 
+ ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))  
+ ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime)) 
+ ']' 
FROM (select c.Name as Hall, b.Title, 
Convert(time,a.StartFilmTime) as StartFilm,  
Convert(time,a.EndFilmTime) as EndTime
from FilmSchedule a 
left join Film b on a.FilmId = b.Id 
left join Room c on a.RoomId = c.Id 
where a.ApproveStatus = 1 and a.Status = 1 and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
) b 
Where a.Hall = b.Hall and a.Title = b.Title 
FOR XML PATH('')),1,1,'') As ShowTime  
from (select c.Name as Hall, b.Title, 
Convert(time,a.StartFilmTime) as StartFilm,  
Convert(time,a.EndFilmTime) as EndTime
from FilmSchedule a 
left join Film b on a.FilmId = b.Id 
left join Room c on a.RoomId = c.Id 
where a.ApproveStatus = 1 and a.Status = 1 and CONVERT(date, a.StartFilmTime) =  '05-06-2015'
Order by a.StartFilmTime
) a 
group by a.Hall, a.Title

i get the error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Help me! pls

Aucun commentaire:

Enregistrer un commentaire