SQL Server Error Messages - Msg 8155

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