i use Order By Used with Case Statement and may be this is the reason for which i am getting error Windowed functions can only appear in the SELECT or ORDER BY clauses.
here is my complete sql where i use Row_Number function for pagination with sorting. please guide me where i made the mistake.
DECLARE @StartIndex INT
DECLARE @EndIndex INT
DECLARE @SortColumn VARCHAR(MAX)
DECLARE @SortDirection VARCHAR(MAX)
SET @StartIndex = 1
SET @EndIndex = 20
SET @SortColumn = 'OrderID'
SET @SortColumn = 'A'
SELECT * FROM Orders
WHERE
ROW_NUMBER() OVER
(
ORDER BY
CASE (@SortColumn + ':' + @SortDirection)
WHEN 'OrderID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
WHEN 'OrderID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
WHEN 'CustomerID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID ASC)
WHEN 'CustomerID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID DESC)
WHEN 'EmployeeID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID ASC)
WHEN 'EmployeeID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID DESC)
WHEN 'OrderDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate ASC)
WHEN 'OrderDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate DESC)
WHEN 'RequiredDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.RequiredDate ASC)
WHEN 'RequiredDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.RequiredDate DESC)
WHEN 'ShippedDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
WHEN 'ShippedDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
WHEN 'ShipVia:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipVia ASC)
WHEN 'ShipVia:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipVia DESC)
WHEN 'Freight:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.Freight ASC)
WHEN 'Freight:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.Freight DESC)
WHEN 'ShipName:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipName ASC)
WHEN 'ShipName:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipName DESC)
WHEN 'ShipAddress:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipAddress ASC)
WHEN 'ShipAddress:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipAddress DESC)
WHEN 'ShipCity:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCity ASC)
WHEN 'ShipCity:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCity DESC)
WHEN 'ShipRegion:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipRegion ASC)
WHEN 'ShipRegion:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipRegion DESC)
WHEN 'ShipPostalCode:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipPostalCode ASC)
WHEN 'ShipPostalCode:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipPostalCode DESC)
WHEN 'ShipCountry:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCountry ASC)
WHEN 'ShipCountry:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.ShipCountry DESC)
END
) BETWEEN (@StartIndex - 1) * @EndIndex + 1 AND @StartIndex * @EndIndex
/* AND more conditions ... */
ORDER BY
CASE WHEN @SortDirection = 'A' THEN
CASE @SortColumn
WHEN 'OrderID' THEN OrderID
WHEN 'CustomerID' THEN CustomerID
WHEN 'EmployeeID' THEN CustomerID
WHEN 'OrderDate' THEN CustomerID
WHEN 'RequiredDate' THEN CustomerID
WHEN 'ShippedDate' THEN CustomerID
WHEN 'ShipVia' THEN CustomerID
WHEN 'Freight' THEN CustomerID
WHEN 'ShipName' THEN CustomerID
WHEN 'ShipAddress' THEN CustomerID
WHEN 'ShipCity' THEN CustomerID
WHEN 'ShipRegion' THEN CustomerID
WHEN 'ShipPostalCode' THEN CustomerID
WHEN 'ShipCountry' THEN CustomerID
END
END,
CASE WHEN @SortDirection = 'D' THEN
CASE @SortColumn
WHEN 'OrderID' THEN OrderID
WHEN 'CustomerID' THEN CustomerID
WHEN 'EmployeeID' THEN CustomerID
WHEN 'OrderDate' THEN CustomerID
WHEN 'RequiredDate' THEN CustomerID
WHEN 'ShippedDate' THEN CustomerID
WHEN 'ShipVia' THEN CustomerID
WHEN 'Freight' THEN CustomerID
WHEN 'ShipName' THEN CustomerID
WHEN 'ShipAddress' THEN CustomerID
WHEN 'ShipCity' THEN CustomerID
WHEN 'ShipRegion' THEN CustomerID
WHEN 'ShipPostalCode' THEN CustomerID
WHEN 'ShipCountry' THEN CustomerID
END
END DESC
Aucun commentaire:
Enregistrer un commentaire