the order by clause is invalid in views inline functions ...

Majid Shahabfar

SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber

FROM	aspnet\_Users INNER JOIN aspnet\_Membership ON aspnet\_Users.UserId = aspnet\_Membership.UserId INNER JOIN
                  PD\_Users INNER JOIN PD\_Cities ON PD\_Users.CityID = PD\_Cities.CityId ON aspnet\_Users.UserName = PD\_Users.UserName
WHERE (@LastName = PD\_Users.LastName  OR (@LastName = '' AND 1=1))    
      AND (@UserName = PD\_Users.UserName OR (@UserName = '' AND 1=1))
      AND (@CityID = PD\_Users.CityID OR (@CityID = -1 AND 1=1))
      AND (@UserStatus = (CASE WHEN aspnet\_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))                
 ORDER BY PD\_Users.FirstName DESC

I got the error: the order by clause is invalid in views inline functions ... do you have any idea?

i j russell

Have you tried typing the error message into Google? I did and I got 94,000 results; Maybe one of those could be of help?

Chris Meech

What exactly are you trying to order? :confused: :confused:

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

SilimSayo

I think you should not try to order intermediate results. You should only end-results.

Majid Shahabfar

order by users FirstName or LastName

Majid Shahabfar

can you change my SQL Statement as you think?

SilimSayo

Actually, my answer was not correct. I think what's wrong with your query is the syntax i.e where the Order By Clause comes in. The syntax should be in form ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

Chris Meech

If you want it ordered by either FirstName or LastName, then why does your original post also have an order by clause here; ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber The ORDER BY in this context is in error.

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

Majid Shahabfar

Thanks Silim it works bt now I have anoter problem when I use ArticleCount I get the Invalid column name error do you hae any idea?