If you’re getting error number 4123 that reads “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server, it’s probably because you’re referring to a named window with the same clause that’s in the named window.
When we use the OVER clause to refer to a named window in SQL Server, we can’t include a clause that’s also present in the named window.
To fix the issue, remove one of the clauses – either the one in the OVER clause or the one in the named window.
Example of Error
Here’s an example of code that produces the error:
SELECT
District,
Name,
Population,
SUM(Population) OVER(win PARTITION BY District) AS "Population"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS (PARTITION BY District);Result:
Msg 4123, Level 15, State 2, Server bd79c358ea10, Line 69 Window element in OVER clause can not also be specified in WINDOW clause.
In this case, I used a PARTITION BY clause in both the OVER clause and the WINDOW clause (which defines the named window).
This is not allowed and so I got the error.
Solution
All I need to do to fix the problem is remove one of the PARTITION BY clauses:
SELECT
District,
Name,
Population,
SUM(Population) OVER win AS "Population"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS (PARTITION BY District);Result:
District Name Population Population -------------------- --------------- ----------- ----------- Capital Region Canberra 322723 322723 New South Wales Sydney 3276207 3993949 New South Wales Newcastle 270324 3993949 New South Wales Central Coast 227657 3993949 New South Wales Wollongong 219761 3993949 Queensland Brisbane 1291117 1805236 Queensland Gold Coast 311932 1805236 Queensland Townsville 109914 1805236 Queensland Cairns 92273 1805236 South Australia Adelaide 978100 978100 Tasmania Hobart 126118 126118 Victoria Geelong 125382 2990711 Victoria Melbourne 2865329 2990711 West Australia Perth 1096829 1096829
In this case I removed the PARTITION BY clause from the OVER clause in the SELECT list.
Note that I also removed the parentheses. If I hadn’t done this, I would’ve seen a different error.
An alternative fix would have been to remove the PARTITION BY clause from the WINDOW clause (while keeping the one in the OVER clause):
SELECT
District,
Name,
Population,
SUM(Population) OVER(win PARTITION BY District) AS "Population"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS ();Mind you, in this case I’m removing all clauses from the WINDOW function, which kind of defeats the purpose of even having a named window.