No column name was specified for column 2 of 't' T_SQL error

Question

PLease i want to return only debtorid from this T-SQL . It gives the error " No column name was specified for column 2 of 't'." My query is below.

 with t as 
(SELECT debtorid,MAX(dbo.FollowUp.followupdate) FROM dbo.FollowUp WITH (NOLOCK) WHERE
( dbo.FollowUp.FollowUpDate >= '01-01-2011 00:00:00:000' and dbo.FollowUp.FollowUpDate <= '01-13-2014 23:59:59.000'
and Status = 'PTP')  GROUP BY [Status], DebtorId)

SELECT t.debtorid FROM t;

WIth my little knowledge , i thought the above should work fine for me. however, it didn't.Any help would be appreciated.

Why are you computing an aggregate if you're just going to ignore it? Do you explicitly want to ignore anything with a follow up date of, say, `2014-01-13T23:59:59.443`, or have you just not considered that using `<` and a value of `2014-01-14T00:00:00` would be cleaner (especially since that can be shortened to `20140114`)? — Damien_The_Unbeliever, Jan 13 '14 at 07:02

score 2 · Answer 1 · answered Jan 13 '14 at 06:13

You need to add a name to your second column, e.g.:

with t as 
(SELECT debtorid, MAX(dbo.FollowUp.followupdate) maxFollowup 
 FROM dbo.FollowUp WITH (NOLOCK) 
 WHERE dbo.FollowUp.FollowUpDate >= '01-01-2011 00:00:00:000' 
       and dbo.FollowUp.FollowUpDate <= '01-13-2014 23:59:59.000'
       and Status = 'PTP'
GROUP BY [Status], DebtorId)

SELECT t.debtorid FROM t;

score 1 · Accepted Answer · answered Jan 13 '14 at 06:25

1

You are using Common Table Expressions or CTE, in CTE you must have column name as per msdn. You can also refer for CTE best practices:

http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx http://blog.sqlauthority.com/2011/05/10/sql-server-common-table-expression-cte-and-few-observation/

;with t as 
(
    SELECT FWP.debtorid
        ,MAX(FWP.followupdate) followupdate 
    FROM dbo.FollowUp  FWP WITH (NOLOCK) 
    WHERE
    ( 
        FWP.FollowUpDate >= '01-01-2011 00:00:00:000' 
         and FWP.FollowUpDate <= '01-13-2014 23:59:59.000'
         and FWP.Status = 'PTP'
    )  GROUP BY FWP.[Status], FWP.DebtorId
)
SELECT t.debtorid FROM t;

answered Jan 13 '14 at 06:25

Pranav Singh

17,079
30
77
104

Sorry Pranav. Please how do i use this as a subquery? I tried but its given me error. When i run the query independently it works. However, cant incorporate in another query. – Nuru Salihu Jan 13 '14 at 07:22
I think this will help :http://social.msdn.microsoft.com/Forums/en-US/4e269e7e-6411-4134-a181-63c1280b4c7e/how-to-use-sql-hints-within-ctesubquery http://stackoverflow.com/questions/6107865/sql-server-user-cte-in-subquery http://www.4guysfromrolla.com/webtech/071906-1.shtml http://www.4guysfromrolla.com/webtech/071906-1.shtml – Pranav Singh Jan 13 '14 at 07:25
This will not work straight forward CTE will be above & you can use union in below with CTE alias – Pranav Singh Jan 13 '14 at 07:27

No column name was specified for column 2 of 't' T_SQL error

2 Answers2