In this blog post, let’s learn about the error message “138 – Correlation clause in a subquery not permitted.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
138 – Correlation clause in a subquery not permitted.
Reason for the Error
SQL Server Error Msg 138 – “Correlation clause in a subquery not permitted” occurs when you attempt to reference a column from an outer query inside a subquery that has a correlation clause. A correlation clause specifies a condition that correlates the subquery to the outer query, and it can reference a column from the outer query.
There are several reasons why this error may occur:
- Incorrect syntax: The most common reason for this error is an incorrect syntax. If you misspell the column name, or use an incorrect alias, then SQL Server will not be able to find the column and will raise the error.
- Ambiguous column names: If a column name is used in both the outer query and subquery, and it is not qualified with a table alias, then SQL Server will raise the error. This is because SQL Server will not be able to determine which column to use.
- Subquery column scope: If a subquery column is referenced outside of the subquery, then SQL Server will raise the error. This is because the column is only available within the scope of the subquery.
Solution
- Verify the syntax: Verify that the syntax is correct and all the column names and aliases are spelled correctly.
- Qualify column names: Qualify column names with table aliases to avoid ambiguity.
- Use derived tables: Use derived tables instead of subqueries. Derived tables are subqueries that are used as a table in the outer query.
