Avatar of Zack
Zack
Flag for Australia asked on

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 #BudgetTransTmp
Select  
        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 MONTH


INTO   #BudgetTransTmp
FROM   BudgetTransactionLine                    AS BTLine


    --Get Display value
    INNER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION AS COMBINATION
    ON  COMBINATION.RECID                                           = BTLine.LEDGERDIMENSION
GROUP BY  
            BTLine.LedgerDimension,
            YEAR(BTLine.DATE),
            MONTH(BTLine.DATE),
         COMBINATION.DISPLAYVALUE,
         BTLine.TRANSACTIONCURRENCYAMOUNT
ORDER BY RowNumber

Open in new window

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

Avatar of undefined
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:

SUM(BTLine.TransactionCurrencyAmount) 

Open in new window

Zack

ASKER
Hi Ryan,

I would like to try to keep the partition if I could for sorting purposes.

Thank you. 
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Zack

ASKER
Can't argue that point :) 
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck