jeudi 24 septembre 2015

Order By Used with Case Statement

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