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

I have this query, but its not working as it should,

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

select 
    c.duration, 
    c.totalbookings, 
    d.bkdqty 
from
    c 
    inner join d 
    on c.duration = d.duration

when I run this, I am getting

Msg 8155, Level 16, State 2, Line 1 No column was specified for column 2 of 'd'.

Can any one tell me what am I doing wrong?

Also, when I run this,

with c as (select 
               month(bookingdate) as duration, 
               count(*) as totalbookings 
           from 
               entbookings
           group by month(bookingdate)
          ),
     d as (select 
               month(clothdeliverydate), 
               SUM(CONVERT(INT, deliveredqty)) 
           FROM 
               barcodetable
           where 
               month(clothdeliverydate) is not null
               group by month(clothdeliverydate)
          )

select 
    c.duration, 
    c.totalbookings, 
    d.bkdqty 
from
    c 
    inner join d 
    on c.duration = d.duration

I get

Msg 8155, Level 16, State 2, Line 1 No column was specified for column 1 of 'd'. Msg 8155, Level 16, State 2, Line 1 No column was specified for column 2 of 'd'.

Answers


[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

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
)

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

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!


Quite an intuitive error message - just need to give the columns in d names

Change to either this

d as 
 (
  select                 
     [duration] = month(clothdeliverydate),                 
     [bkdqty] = SUM(CONVERT(INT, deliveredqty))             
  FROM                 
     barcodetable            
  where                 
     month(clothdeliverydate) is not null                
  group by month(clothdeliverydate)           
 ) 

Or you can explicitly declare the fields in the definition of the cte:

d ([duration], [bkdqty]) as 
 (
  select                 
     month(clothdeliverydate),                 
     SUM(CONVERT(INT, deliveredqty))             
  FROM                 
     barcodetable            
  where                 
     month(clothdeliverydate) is not null                
  group by month(clothdeliverydate)           
 ) 

Just add an alias name as follows sum(totalitems) as totalitems.


evidently, as stated in the parser response, a column name is needed for both cases. In either versions the columns of "d" are not named.

in case 1: your column 2 of d is sum(totalitems) which is not named. duration will retain the name "duration"

in case 2: both month(clothdeliverydate) and SUM(CONVERT(INT, deliveredqty)) have to be named


Need Your Help

Allowing a child Iframe to call a function on its parent window from a different domain

javascript iframe cross-domain

I have made a page which gets loaded in an IFrame and it needs to call a function on the parent page after it finishes loading.

What is the best time to ask questions?

discussion stack-overflow questions

What's the best time of day, or weekday, to ask questions on Stack Overflow? I'd like to know if there might be a way to maximize the exposure or the amount/quality of responses.