我得到“ORDER BY子句在视图,内联函数,派生表,子查询和公用表表达式中无效,除非还指定了TOP或FOR
XML.错误与以下代码.我最初有两张桌子,ADSAREAS&类别.当我删除CATEGORIES表时,我开始收到此错误.
Select Case SIDX
Case "ID" : sqlCONT1 = " AdsAreasID"
Case "Page" : sqlCONT1 = " AdsAreasName"
Case Else : sqlCONT1 = " AdsAreasID"
End Select
Select Case SORD
Case "asc" : sqlCONT2 = " ASC"
Case "desc" : sqlCONT2 = " DESC"
Case Else : sqlCONT2 = " ASC"
End Select
''# search feature --->
Select Case SEARCHFIELD
Case "ID" : sqlSFIELD = "AND AdsAreasID"
Case "Ads Areas" : sqlSFIELD = "AND AdsAreasName"
Case Else : sqlSFIELD = ""
End Select
Select Case SEARCHOPER
Case "eq" : sqlSOPER = " = " & SEARCHSTRING
Case "ne" : sqlSOPER = " <> " & SEARCHSTRING
Case "lt" : sqlSOPER = " <" & SEARCHSTRING
Case "le" : sqlSOPER = " <= " & SEARCHSTRING
Case "gt" : sqlSOPER = " >" & SEARCHSTRING
Case "ge" : sqlSOPER = " >= " & SEARCHSTRING
Case "bw" : sqlSOPER = " LIKE '" & SEARCHSTRING & "%' "
Case "ew" : sqlSOPER = " LIKE '%" & SEARCHSTRING & "' "
Case "cn" : sqlSOPER = " LIKE '%" & SEARCHSTRING & "%' "
Case Else : sqlSOPER = ""
End Select
''# search feature --->
sql = "SELECT * FROM ( SELECT A.AdsAreasID,A.AdsAreasName,ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row"
sql = sql & " FROM ADSAREAS A"
sql = sql & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &") ORDER BY" & sqlCONT1 & sqlCONT2
Set objXML = objConn.Execute(sql)
解决方法
您将ORDER BY子句移动到内部查询以重写它.在WHERE子句之后添加括号(和标识符),以便ORDER BY适用于外部SELECT.
sql = "SELECT * FROM ( SELECT A.AdsAreasID,ROW_NUMBER() OVER (ORDER BY A.AdsAreasID) As Row" sql = sql & " FROM ADSAREAS A" sql = sql & " WHERE Row > ("& RecordsPageSize - RecordsPerPage &") AND Row <= ("& RecordsPageSize &")) inner ORDER BY" & sqlCONT1 & sqlCONT2
