It''s funny but interesting

Question

Post reply

It''s funny but interesting

Yuvraj Powar

SSC Veteran

Points: 272
More actions
January 11, 2005 at 7:55 am

#61218

I have a column "4Blocker_Date" in Table TempTab.
When I execute "Select * from TempTab"
It returns me all columns with all records of TempTab table.
But whenI try execute follwing query
"Select Distinct 4Blocker_Date From TempTab"
The above query returns only one record with value as "4".SQL server considering it as 4 as value and Blocker_date is aliyas name for that column but in TempTab I have column as a 4Blocker_Date and above query is not selecting values from 4Blocker_Date column.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

Try:

Select Distinct [4Blocker_Date]

From TempTab

And in the future, avoid using numeric characters at the beginning of column names... As you can see, SQL Server does not like it

--
Adam Machanic
whoisactive

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

It's not that strange. '4Blocker_Date' is an invalid name as it begins with a number, so I'm not surprised it behaves "funny".

What happens if you execute "Select Distinct [4Blocker_Date] From TempTab" ?

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

One might think, that when SQL Server doesn't like, why did it let the table obviously get created at all? Isn't that the original error here?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Not really, imo the original error is (well, by all means imho the fact that you are allowed to use square brackets is the 'true' blame) - but... the original error is on the creator of the column in the first place.

Once you use square brackets, you must continue to always use square brackets.. You simply can't create a table with a columnname like '4Blocker_Date' without bracketing the name like [4Blocker_Date]. If you try, SQL Server will flatly deny to create such a table.

The problem with using these is that you may unknowingly create invalid names that later cause all kinds of problems, since square bracketing let's you do just that - stuff you should not do..

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

the original error is on the creator of the column in the first place

Allow for something, and sooner or later someone smart will do it. No matter if intentionally or not.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]