locked
ORDER BY not allowed on Azure SQL RRS feed

  • Question

  • Dear all,

    I have the follwoing SQL server store procedure that I have tested and created under my local SQL server 2012

    CREATE PROCEDURE [dbo].[RPT_GetRemainingStockByProduct] 
    	-- Add the parameters for the stored procedure here
    	@StoreID int 
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    SELECT *,
    SUM(Remaining) OVER (PARTITION BY StoreID ORDER BY ProductID) AS TotalRemainingAtStore
    FROM
    (
    	SELECT        
    	P.StoreID, 
    	P.InventoryDate,
    	P.ProductID, 
    	SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight, 
    	SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
    	SUM(PIT.Quantity * P.ItemUnitWeight)-SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS Remaining,
        Production.UnitMeasure.UnitMeasureCode,
    	Sales.Store.Name,
    	LOC.MaxQuantity as MaxStorage
    	
    
    FROM   Sales.Store INNER JOIN
           Production.ProductItem AS PIT INNER JOIN
           Production.Product AS P ON PIT.ProductID = P.ProductID INNER JOIN
           Production.UnitMeasure ON P.WeightUnitMeasureID = Production.UnitMeasure.UnitMeasureID ON Sales.Store.BusinessEntityID = P.StoreID
    	    INNER JOIN
             Production.Location AS LOC ON Sales.Store.BusinessEntityID = LOC.StoreID AND P.LocationID = LOC.LocationID AND P.LocationID = LOC.LocationID
    
    
    WHERE        (P.IsDeleted = 0) AND (PIT.IsDeleted = 0) AND P.StoreID=@StoreID
    GROUP BY P.StoreID,P.InventoryDate, P.ProductID, Production.UnitMeasure.UnitMeasureCode,Sales.Store.Name,LOC.MaxQuantity
    )t
    END
    

    When trying to create that store procs under my SQL azure db, I get an error message saying that ORDER BY is not allowed in line : SUM(Remaining) OVER (PARTITION BY StoreID ORDER BY ProductID) AS TotalRemainingAtStore

    Any idea ?

    regards

    • Moved by Eric__Zhang Friday, November 27, 2015 2:02 AM Move to a proper forum for better result
    Thursday, November 26, 2015 2:28 PM

Answers

All replies