No column name was specified... really? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

No column name was specified... really?

Author

Topic

Signaljunkie
Starting Member

4 Posts

Posted - 2011-04-30 : 16:59:50

I'm sure I'm just missing something. I'm new to subqueries/WITH.

Error:

 
No column name was specified for coloumn 2 of 'xref'.

Intention:
To list some customers by their SIN numbers, ignoring near-duplicate entries (some have the same names, some have Bob/Robert, etc)

Query:


WITH xref AS ( 
SELECT cust.sin, MAX(cust.rowno) AS maxrowno
FROM DB.dbo.customers cust
GROUP BY (cust.sin)
)
SELECT cust.name, cust.sin, cust.phone, cust.address
FROM DB.dbo.customers cust, xref
WHERE xref.maxrowno = cust.rowno

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-30 : 18:07:02

You are not on SQL 2000 are you? I ran the following code and it parses and runs without any errors/warnings. If I remove the red text, it gives exactly the same error message that you saw.

create table #customers ([sin] int, rowno int, name varchar(255),phone varchar(255),[address] varchar(255));

WITH xref AS ( 
SELECT cust.sin, MAX(cust.rowno) AS maxrowno
FROM #customers cust
GROUP BY (cust.sin)
)
SELECT cust.name, cust.sin, cust.phone, cust.address
FROM #customers cust, xref
WHERE xref.maxrowno = cust.rowno

Signaljunkie
Starting Member

4 Posts

Posted - 2011-05-03 : 12:53:25

Thanks for your reply, sunitabeck.

I'm using SQL Server 2008 and the query tool from Excel 2003. Just as you say, I would expect it to give me that error message if I removed the 'AS x' clause.

I'm still puzzled by this one. I'll try to reform the query, maybe I can get a clue.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-03 : 12:59:23

You could try specifying the column names in the With also...

WITH xref (sin, maxrowno) AS (

Corey

I Has Returned!!

Signaljunkie
Starting Member

4 Posts

Posted - 2011-05-03 : 13:27:15

quote:
Originally posted by Seventhnight

You could try specifying the column names in the With also...

WITH xref (sin, maxrown

Great call, Corey! That fixed it. I think I'm up against a client limitation, using this old Microsoft Query tool. I tried the original query in Management Studio and it executed correctly without the addition of column names. This works though:

WITH xref (maxrowno, sin) AS (
SELECT MAX(cust.rowno), cust.sin
FROM DB.dbo.customers cust
GROUP BY (cust.sin)
)
SELECT cust.name, cust.sin, cust.phone, cust.address
FROM DB.dbo.customers cust, xref
WHERE xref.maxrowno = cust.rowno

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-03 : 16:52:50

Sweet!

Glad that worked

Corey

I Has Returned!!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-04 : 09:32:37

Join in WHERE clause = naughty.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Signaljunkie
Starting Member

4 Posts

Posted - 2011-05-04 : 14:11:25

Apologies, Mr. Teacher.

WITH xref (maxrowno, sin) AS (
SELECT MAX(cust.rowno), cust.sin
FROM DB.dbo.customers cust
GROUP BY (cust.sin)
)
SELECT cust.name, cust.sin, cust.phone, cust.address
FROM DB.dbo.customers cust
INNER JOIN xref ON xref.maxrowno = cust.rowno

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-05-05 : 08:33:11

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources