getting "No column was specified for column 2 of 'd'" in sql server cte?

ID : 131402

viewed : 7

Tags : sqlsql-servergroup-bycommon-table-expressioncalculated-columnssql

Top 5 Answer for getting "No column was specified for column 2 of 'd'" in sql server cte?

vote vote

90

You just need to provide an alias for your aggregate columns in the CTE

d as (SELECT     duration,     sum(totalitems) as sumtotalitems FROM     [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty group by duration ) 
vote vote

87

[edit]

I tried to rewrite your query, but even yours will work once you associate aliases to the aggregate columns in the query that defines 'd'.


I think you are looking for the following:

First one:

select      c.duration,      c.totalbookings,      d.bkdqty  from     (select                 month(bookingdate) as duration,                 count(*) as totalbookings             from                 entbookings            group by month(bookingdate)     ) AS c      inner join      (SELECT                 duration,                 sum(totalitems) 'bkdqty'            FROM                 [DrySoftBranch].[dbo].[mnthItemWiseTotalQty] ('1') AS BkdQty            group by duration     ) AS d      on c.duration = d.duration 

Second one:

select      c.duration,      c.totalbookings,      d.bkdqty  from     (select                 month(bookingdate) as duration,                 count(*) as totalbookings             from                 entbookings            group by month(bookingdate)     ) AS c      inner join      (select                 month(clothdeliverydate) 'clothdeliverydatemonth',                 SUM(CONVERT(INT, deliveredqty)) 'bkdqty'            FROM                 barcodetable            where                 month(clothdeliverydate) is not null                group by month(clothdeliverydate)     ) AS d      on c.duration = d.duration 
vote vote

80

I had a similar query and a similar issue.

SELECT     * FROM     Users ru     LEFT OUTER JOIN      (         SELECT ru1.UserID, COUNT(*)         FROM Referral r         LEFT OUTER JOIN Users ru1 ON r.ReferredUserId = ru1.UserID         GROUP BY ru1.UserID     ) ReferralTotalCount ON ru.UserID = ReferralTotalCount.UserID 

I found that SQL Server was choking on the COUNT(*) column, and was giving me the error No column was specified for column 2.

Putting an alias on the COUNT(*) column fixed the issue.

  SELECT         *     FROM         Users ru         LEFT OUTER JOIN          (             SELECT ru1.UserID, COUNT(*) AS -->MyCount<--             FROM Referral r             LEFT OUTER JOIN Users ru1 ON r.ReferredUserId = ru1.UserID             GROUP BY ru1.UserID         ) ReferralTotalCount ON ru.UserID = ReferralTotalCount.UserID 
vote vote

66

Because you are creatin a table expression, you have to specify the structure of that table, you can achive this on two way:

1: In the select you can use the original columnnames (as in your first example), but with aggregates you have to use an alias (also in conflicting names). Like

sum(totalitems) as bkdqty 

2: You need to specify the column names rigth after the name of the talbe, and then you just have to take care that the count of the names should mach the number of coulms was selected in the query. Like:

d (duration, bkdqty)  AS (Select.... )  

With the second solution both of your query will work!

vote vote

59

A single with clause can introduce multiple query names by separating them with a comma but it's mandatory that every column has a name

In this case, the second query has a column without one:

 as (SELECT                 duration,                 sum(totalitems) --**HERE IS A MISSING NAME**  FROM ... 

That's all.

Top 3 video Explaining getting "No column was specified for column 2 of 'd'" in sql server cte?

Related QUESTION?