Help with Query Error

[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

 

[William Vaughn \(MVP\)]

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
____________________________________________________________________________________________