Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag 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 
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

Avatar of Zack

ASKER

Hi Ryan,

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

Thank you. 
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zack

ASKER

Can't argue that point :)