Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: General / Classic ASP versions(v3.4.XX)
 Having some trouble with ROW_NUMBER()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bobby131313
Senior Member

USA
1163 Posts

Posted - 15 March 2020 :  22:54:02  Show Profile  Visit bobby131313's Homepage  Reply with Quote
I am on SQL Server 2008 so offset doesn't work. I want to get the last 15 replies to a topic.

	ReplyCountOffset = (Topic_Replies - strPageSize)
' Response.Write ReplyCountOffset
'## Forum_SQL
strSql = "SELECT ROW_NUMBER() OVER(ORDER BY R.DATE) AS ROW, M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_STATUS"
strSql = strSql & ", M.M_TITLE, M.MEMBER_ID, M.M_HOMEPAGE, M.M_LEVEL, M.M_POSTS, M.M_COUNTRY, M.M_AVATAR_URL, M_SUPPORTER, M_MOD_NOTE"
strSql = strSql & ", R.REPLY_ID, R.FORUM_ID, R.R_AUTHOR, R.TOPIC_ID, R.R_MESSAGE, R.R_LAST_EDIT"
strSql = strSql & ", R.R_LAST_EDITBY, R.R_SIG, R.R_STATUS, R.R_DATE, R.R_MOD_NOTE"
if CanShowSignature = 1 then
strSql = strSql & ", M.M_SIG"
end if
strSql2 = " FROM " & strMemberTablePrefix & "MEMBERS M, " & strActivePrefix & "REPLY R "
strSql3 = " WHERE M.MEMBER_ID = R.R_AUTHOR AND ROW > " & ReplyCountOffset
strSql3 = strSql3 & " AND R.TOPIC_ID = " & Topic_ID & " "
' DEM --> if not a Moderator, all unapproved posts should not be viewed.
if AdminAllowed = 0 then
strSql3 = strSql3 & " AND (R.R_STATUS < "
if Moderation = "Y" then
' Ignore unapproved/rejected posts
strSql3 = strSql3 & "2"
else
' Ignore any previously rejected topic
strSql3 = strSql3 & "3"
end if
strSql3 = strSql3 & " OR R.R_AUTHOR = " & MemberID & ")"
end if


I keep getting this error....

Microsoft SQL Server Native Client 10.0 error '80040e14'
Invalid column name 'ROW'.


Everything I've read seems to say it should work. Any ideas?

Switch the order of your title tags

HuwR
Forum Admin

United Kingdom
20579 Posts

Posted - 17 March 2020 :  08:17:39  Show Profile  Visit HuwR's Homepage  Reply with Quote
Your problem is you can't use an aliased column name in a where clause, so you can't do "WHERE M.MEMBER_ID = R.R_AUTHOR AND ROW > " & ReplyCountOffset
you SQL needs to follow this pattern (it should work in 2008)


WITH MemberReplies AS  
(  
    SELECT ROW_NUMBER() OVER (ORDER BY R.DATE) AS RowNumber  …. Other fields here
    FROM FORUM_MEMBERS M JOIN  FORUM_REPLY R ON R.R_AUTHOR = M.MEMBER_ID
)   
SELECT *    
FROM MemberReplies    
WHERE RowNumber > ReplyCountOffset

MVC .net dev/test site | MVC .net running on Raspberry Pi
Go to Top of Page

bobby131313
Senior Member

USA
1163 Posts

Posted - 17 March 2020 :  16:22:02  Show Profile  Visit bobby131313's Homepage  Reply with Quote
I'll give it try, thank you!

Switch the order of your title tags
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000 Version 3.4.07