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

Question

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'.

Impediment

do you still need to know how to had the netBalance ? – Felly

Answer 1

[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

Pena

Nope! this is not working, for the first one, it showed Msg 8155, Level 16, State 2, Line 1 No column was specified for column 2 of 'd'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'bkdqty'. for 2nd one it showed 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'. Msg 207, Level 16, State 1, Line 23 Invalid column name 'duration'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'bkdqty'. – Impediment

The error is about not naming the aggregate columns in the query that defines 'd' in both the places. I tried to correct it, can you verify? – Pena

f*ck yeah man! It worked! thanks a lot! :-) you are a life savior. One thing though it would be month(clothdeliverydate) 'duration' for it to work, otherwise it keeps saying can't find column duration. – Impediment

I just had to add aliases to all columns on which a function was used. Even on RTRIM() (SQL 2008). And the error was gone. – Brewery

This was it! Thanks! – Susann

Answer 2

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
)

Stomy

tanks! wierd that you get a default col name on agg expressions in many cases..... – Adiel

Answer 3

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

Egin

same for me (well with the AVG function) but after realizing what the problem was I accidentally added the AS AvgValue to the table name instead of the 'column'. So I ended up with SELECT Something, Something2, AVG(Value) FROM Orders AS AvgValue. Looks super stupid here, but the way my SQL was formatted it snuck through and didn't look so obvious! – Granoff

I looked waaay too long before finding this, thanks. – Photomultiplier

Answer 4

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.

Alcina

Answer 5

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!

Brambling

Answer 6

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)           
 ) 

Aylmar

Answer 7

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

Sheik

Answer 8

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

Santos

Answer 9

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'. ANSWER:

ROUND(AVG(CAST(column_name AS FLOAT)), 2) as column_name

Malediction