Bug, Feature or Intellisense? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Bug, Feature or Intellisense?

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-25 : 06:12:01

Is this a bug, feature or just plain Intellisense?

This topic started originally here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69303[/url]

-- prepare test data
declare	@table table (s int, item int)

insert	@table
select	1, 1 union all
select	1, 2 union all
select	1, 3 union all
select	2, 2 union all
select	3, 5

-- do the work
select		t1.s s1,
		t2.s s2,
		1.0 * sum(case when t1.item = t2.item then 1 else 0 end) / count(distinct t1.item) matching
from		@table t1
inner join	@table t2 on t1.s <> t2.s
group by	t1.s,
		t2.s
order by	z.s1,
		z.s2

I have no reference to z table anywhere, but the code runs ok!

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-25 : 06:18:29

I think this is mix of both bug/feature...I don't know

But here is another thing about ORDER BY which I just now learned as I saw it used in some of my app's code:

Select Col1, Col2, ...
from SomeTable
Order by 1,2

This seems to be position-based sorting !

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-07-25 : 06:36:19

"This seems to be position-based sorting"

Yeah, its documented!

"BoL:order_by_expression Specifies a column on which to sort. A sort column can be specified as a name or column alias (which can be qualified by the table or view name), an expression, or a nonnegative integer representing the position of the name, alias, or expression in select list."

"I have no reference to z table anywhere, but the code runs ok!"

I had one of these this morning (again!)

I think its the bit about allowing sorting by an Alias name. This "feature" only works for alias names in the select list (it won't work for a column in the select list which is not actually aliased).

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-25 : 06:40:59

Actually MS SQL Server is more foregiving when it is younger

At age 2005, it throws out this error.

quote:
Server: Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "z.s1" could not be bound.
Server: Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "z.s2" could not be bound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 05:26:27

Here is an update due to Kristen's discovery

-- prepare test data
declare	@table table (s int, item int, s1 int)

insert	@table
select	1, 1, 0 union all
select	1, 2, 0 union all
select	1, 3, 0 union all
select	2, 2, 0 union all
select	3, 5, 0

-- do the work
select		t1.s s1,
		t2.s s2,
		1.0 * sum(case when t1.item = t2.item then 1 else 0 end) / count(distinct t1.item) matching
from		@table t1
inner join	@table t2 on t1.s <> t2.s
group by	t1.s,
		t2.s
order by	z.s1,
		z.s2

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources