CODEWITHSUNDEEP
sqlsql-serverxmlsql-order-by
Kull
Kull

Reputation: 45

Issue with Order By with FOR XML in T-sql (The ORDER BY clause is invalid in views, inline functions, derived tables)

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 please! (I have used FOR XML?)

Upvotes: 1

Views: 162

Answers (1)

StuartLC
StuartLC

Reputation: 107237

Although your query does use FOR XML (for the GROUP_CONCAT workaround), you are applying the order by outside of the derived table that uses FOR XML, hence the error.

Given that you aren't including start date directly in the final select (although you are composing it as part of the STUFF ShowTime column), you also can't ORDER BY StartFilm in the final GROUP BY either, as the column would otherwise need to be included in the GROUP BY or as an aggregated column.

What you can do is move the ORDER BY into the STUFF and then order by the derived column ShowTime (since your query only runs for one given day, and StartFilmTime is the first part of the STUFFED composed column).

At the same time, I would DRY up the repetition on the derived table with a CTE:

WITH cteFiltered AS
    (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'
    )
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
            cteFiltered b
        Where 
            a.Hall = b.Hall and a.Title = b.Title 
        order by b.StartFilm -- ***
        FOR XML PATH('')),1,1,'') As ShowTime  
from 
    cteFiltered a 
group by a.Hall, a.Title
order by ShowTime; -- *** Hour is first (assuming 24H format) and only one day

Upvotes: 2

Related Questions