SQL - ORDER BY list of RANGE window frame exceeded limit
Hi EE,
I have the following query:
IF OBJECT_ID('tempdb..#BudgetTransTmp') IS NOT NULL DROP TABLE #BudgetTransTmpSelect Row_number() over(ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE)) as RowNumber, COMBINATION.DISPLAYVALUE, BTLine.LedgerDimension AS LedgerDimension, MIN(BTLine.TransactionCurrencyAmount) AS Amount, SUM(BTLine.TransactionCurrencyAmount) OVER (PARTITION BY BTLine.LedgerDimension ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE),BTLine.LedgerDimension,COMBINATION.DISPLAYVALUE ) As SUM, --OVER (ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE),BTLine.LedgerDimension,COMBINATION.DISPLAYVALUE ) , YEAR(BTLine.DATE) AS Year , MONTH(BTLine.DATE) AS MONTHINTO #BudgetTransTmpFROM BudgetTransactionLine AS BTLine --Get Display value INNER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION AS COMBINATION ON COMBINATION.RECID = BTLine.LEDGERDIMENSIONGROUP BY BTLine.LedgerDimension, YEAR(BTLine.DATE), MONTH(BTLine.DATE), COMBINATION.DISPLAYVALUE, BTLine.TRANSACTIONCURRENCYAMOUNTORDER BY RowNumber
Error msg: Msg 8729, Level 16, State 1, Line 3 ORDER BY list of RANGE window frame has total size of 1016 bytes. The largest size supported is 900 bytes.
How would I restructure my query to fix this issue, any assistance is appreciated?
Thank you
SQLMicrosoft SQL Server
Last Comment
Zack
8/22/2022 - Mon
Ryan Chong
try replace:
SUM(BTLine.TransactionCurrencyAmount) OVER (PARTITION BY BTLine.LedgerDimension ORDER BY YEAR(BTLine.DATE),MONTH(BTLine.DATE),BTLine.LedgerDimension,COMBINATION.DISPLAYVALUE )
Open in new window
with:
Open in new window