CODEWITHSUNDEEP
t-sql
nnmmss
nnmmss

Reputation: 2992

No Column was specified for column 1 for cte3

I get this error message 

declare @i as int 
declare @CategoryList varchar(1000);
set @i=294;
with 
cte1 as (
    select * from Project where Id=@i
),
cte2 as (
    select dbo.CableProperty.* 
      from CableProperty 
     inner join cte1 on dbo.CableProperty.ProjectId = cte1.Id
), 
cte3 as (
    select @CategoryList = coalesce(@CategoryList + ', ', '') + FromBay 
      from cable 
     inner join cte2 on dbo.cable.CablePropertyId = cte2.Id
    select @CategoryList
)
select * from cte3

the above code is a very simple of what i want to do. but the concept are like that. why i get that error? Thank you

Upvotes: 0

Views: 214

Answers (2)

chaitanya.moguluri
chaitanya.moguluri

Reputation: 240

I think it is not possible to have two independent SELECT statements in a CTE (Present in CTE3). And you will be getting only one column from CTE3[AliasName], so I guess we don't have to specify SELECT @CategoryList.

DECLARE @i AS INT
DECLARE @CategoryList VARCHAR(1000);
SET @i = 294;

;WITH CTE AS (
    SELECT * FROM Project WHERE ID = @i 
)
,CTE2 AS (
    SELECT * FROM CableProperty INNER JOIN CTE1 ON ProjectID = CTE1.ID
)
,CTE3 AS (
    SELECT COALESCE(@CategoryList + ', ','') + FromBay [AliasName] FROM Cable
    INNER JOIN CTE2 ON CablePropertyID = CTE2.ID    
)
SELECT * FROM CTE3

Try this. Hope it helps.

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

declare @i as int 
declare @CategoryList varchar(1000);
set @i=294;

select @CategoryList = coalesce(@CategoryList + ', ', '') + FromBay 
  from cable 
  join CableProperty 
    on cable.CablePropertyId = CableProperty.Id
  join Project 
    on CableProperty.ProjectId = Project.Id
   and Project.Id = @i
select @CategoryList

what table has FromBay?

Upvotes: 0

Related Questions