|
|
Error Message
Server: Msg 8155, Level 16, State 2, Line 1
No column name was specified for column <Column Index>
of '<Object Name>'.
Causes
There’s a couple of ways of how this error can be encountered. The first method is through the use of sub-queries wherein the sub-query is used in a JOIN statement. To illustrate, here’s a simple query that will generate this error message:
|
SELECT *
FROM [dbo].[Sales] A INNER JOIN (SELECT [SalesDate], MAX([SalesAmount])
FROM [dbo].[Sales]
GROUP BY [SalesDate]) B
ON A.[SalesDate] = B.[SalesDate]
GO
Msg 8155, Level 16, State 2, Line 5
No column name was specified for column 2 of 'B'.
Another way of encountering this error message is with the use of row constructor or table-valued constructor as derived table. Introduced in SQL Server 2008, row constructors consist of a single VALUES clause with multiple value lists enclosed in parenthesis and separated by commas. The following script illustrates how this error message may be generated with a row constructor or table-valued constructor:
SELECT *
FROM (VALUES ( 'FL', 'Florida' ),
( 'NY', 'New York' )) [USState]
GO
Msg 8155, Level 16, State 2, Line 3
No column name was specified for column 1 of 'USState'.
Msg 8155, Level 16, State 2, Line 3
No column name was specified for column 2 of 'USState'.
Solution / Work Around:
As the message suggests, a column name has to be specified in either a sub-query or in a row constructor or table-valued constructor used as derived table. The column name is not required if a query that uses a group function such as MAX or AVG is executed by itself but once that query is used as a sub-query in a JOIN statement, then a column name has to be specified for the output of the group function.
In the first scenario, supplying a column name in the MAX([SalesAmount]) group function will overcome this error:
SELECT *
FROM [dbo].[Sales] A INNER JOIN (SELECT [SalesDate], MAX([SalesAmount]) AS [MaxSalesAmount]
FROM [dbo].[Sales]
GROUP BY [SalesDate]) B
ON A.[SalesDate] = B.[SalesDate]
GO
Similarly, in the second scenario, providing column names for each value included in the table-valued constructor will overcome this error:
SELECT *
FROM (VALUES ( 'FL', 'Florida' ),
( 'NY', 'New York' )) [USState] ( [Code], [Name] )
GO
The output of this query is as follows:
Code Name
----- ------------
FL Florida
NY New York
|