(No column name) in Select statement

Question

Post reply

(No column name) in Select statement

sol.nt

SSCrazy

Points: 2062
More actions
January 19, 2010 at 9:05 pm

#229262

Hi all,
Select * from ( Select 1 ) tbl
and error show that:
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'tbl'.
I knew exactly what the error is.
But are there anyway else to make the tsql above to run without use alias like:
Select * from ( Select 1 as colname) tbl
I mean something to the outside Select statement will auto generate a column name.
Thanks and regards
Sol

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 1

Apart from dynamic sql, i dont see how this can be done.

Here's an example...

declare @s-2 nvarchar(200)

declare @w nvarchar(200)

declare @col nvarchar(200)

declare @sql nvarchar(200)

set @s-2 ='Select * from '

set @w = 'select 1 '

set @col='as a'

set @sql= @s-2 +'('+@w+' '+@col+')'+'a'

exec (@sql)

you can set the @col to anything you what, temp list etc, you should be able to achieve your output.

sol.nt SSCrazy Points: 2062 More actions · Answer 2

Use dynamic sql is not what i mean in the first post.

If you don't know how much column which the result set will return and which column have a name or not then you can not use alias in dynamic sql.

Regards

Sol

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

sol-356065 (1/19/2010)
I mean something to the outside Select statement will auto generate a column name.

Why would you want such a thing ?

Imagine if it 'auto-generated' a name such as 'col1' , then the query changes and now the new name for col1 is col2.

What a mess!

Clear Sky SQL
My Blog[/url]

sol.nt SSCrazy Points: 2062 More actions · Answer 4

Dave Ballantyne (1/20/2010)
sol-356065 (1/19/2010)
I mean something to the outside Select statement will auto generate a column name.
Why would you want such a thing ?
Imagine if it 'auto-generated' a name such as 'col1' , then the query changes and now the new name for col1 is col2.
What a mess!

I writing something to auto create table from a result set and when the result has not column name, the script is failed.

So i am finding the way to workaround this problem. What i want is a column must have column name, any name, not (No column name) as sql server showed us.

So if new name is col1 or col2 or ... is not a problem for me.

Thanks and regards

Sol

LutzM SSC Guru Points: 107049 More actions · Answer 5

Where do you get that result set from?

Instead of auto-generating column names you should ensure to get a useable result set in the first place...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sol.nt SSCrazy Points: 2062 More actions · Answer 6

lmu92 (1/20/2010)
Where do you get that result set from?
Instead of auto-generating column names you should ensure to get a useable result set in the first place...

I get from some sps which i can not sure what i will have, because they were written by many people.

sol

LutzM SSC Guru Points: 107049 More actions · Answer 7

sol.nt (1/28/2010)

I get from some sps which i can not sure what i will have, because they were written by many people.
sol

I don't understand the business case / concept....

Would you mind sharing a basic example of that rather strange concept?

How are the results from those sp's transferred into a table?

If you don't know the column names nor the number of columns how do you know the context of each column?

Instead of trying to assign a meaningless column name to an unknown number of columns I'd rather try to figure out what's going on in the first place... I'm kinda scared right now... :sick:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

If your columns wouldn't have a name, how would you access those columns in your next SELECT?

As Dave wrote, why not using a incremental default name like col1, col2, ... or a GUID?

Greets

Flo

william.e.johnson4 SSC Rookie Points: 25 More actions · Answer 9

I am also trying to deal with a no column name error message. in my case I wanted to embed an xml fragment (as a column) in another select statement. i.e.

select table1.* from

(select B.partID as '@partID' , B.id AS '@rowid', B.xmlContent as '*'

from xmlfragments B

order by B.id

for xml path) table1

Ultimately I was hoping to create a hierarchy of views where each returned xml fragments towards a complete xml document.

But the create view returns the same message about column 1 not javing a name.

You will notice that the result of the inner query actually seems to return a GUID with a leading 'XML_' for a column name.