Help with Query Error

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

Jonathan Wood

I'm generating some queries on the fly at run time and, when I produce the
following query:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CategoryName) AS RowID,
PadFiles.*, Categories.Title+'::'+Subcategories.Title AS CategoryName FROM
Categories LEFT OUTER JOIN Subcategories ON Categories.ID =
Subcategories.CategoryID RIGHT OUTER JOIN PadFiles ON Subcategories.ID =
PadFiles.CategoryID WHERE Approved=1 AND Categories.ID=3) AS InnerQuery
WHERE RowID BETWEEN @StartRow+1 AND (@StartRow+@MaxRows) ORDER BY RowID

I get the error "Invalid column name 'CategoryName'."

Can someone help me understand why this query is generating this error? Is
there no way to do a ROW_NUMBER() sorted by a dynamic field this way?

Thanks.

Jonathan
 
I don't think you can expect the TSQL compiler to execute concatenation
during the time it resolves object names.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
Back
Top