Invalid column Row No

Question

Hi

I am getting error - Invalid column Row No. on below line

PARTITION BY A."Account" ORDER BY "Row No"

SELECT
ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "Row No",
A."Account" AS "Account",(A."Debit") AS "Debit", 
SUM(A."Debit" - A."Credit") OVER(PARTITION BY A."Account" ORDER BY "Row No") AS [Cumulative Total]

From Master

Thanks

Ramco Ramco · Answer

Hi Getting error - Windowed functions cannot be used in the context of another windowed function or aggregate. Thanks

Daniel Wright · Answer

It seems like you're encountering an issue with the "Invalid column Row No." error in your SQL query. The problem lies in the use of the alias "Row No" within the SUM function's ORDER BY clause. In SQL, when you reference a column alias in the ORDER BY clause that was created in the SELECT list using a window function like ROW_NUMBER(), you typically have to use the original expression instead of the alias. This is because the column alias may not be recognized within the same SELECT statement, especially in certain RDBMS. To address this, you could modify your query like this: SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A.Account) AS "Row No", A.Account AS Account, A.Debit AS Debit, SUM(A.Debit - A.Credit) OVER (PARTITION BY A.Account ORDER BY ROW_NUMBER() OVER (PARTITION BY A.Account ORDER BY A.Account)) AS [Cumulative Total] FROM Master A By replacing "Row No" with ROW_NUMBER() OVER (PARTITION BY A.Account ORDER BY A.Account) within the ORDER BY clause of SUM, you ensure that the window function is used directly where needed. I hope this explanation helps in resolving the error you encountered. If you have any more questions or need further clarification, feel free to ask!