June 5, 2009 at 7:56 am
This problem also occurs in SQL 2000 with any formula the order by clause, but the formula variant works in SQL 2008. Is this a bug or is something going on that makes this a correct result?
Please note the output when the order by clause is changed for the other other one.
create function f
(
@n int
)
returns int
as
begin
return @n
end
GO
set nocount on
create table #n ( n int )
declare @loop int
set @loop = 1
while @loop <= 20
begin
insert into #n select @loop
set @loop = @loop + 1
end
declare @a varchar(8000)
set @a = ''
select @a = @a + convert(varchar, n2.n) + ','
from #n n
inner join #n n2 on n2.n = n.n + 10
where n.n between 1 and 10
--order by dbo.f(n2.n)
order by n2.n
select @a
drop table #n
set nocount off
GO
drop function f
GO
[/code]
June 5, 2009 at 7:58 am
It's a known issue. Order By sometimes messes up the concat.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2009 at 8:18 am
I did find an MS Support article here, but it only says the problem applies to SQL 2000 and SQL 7.
The comments regarding ANSI standards still apply, and the article hasn't been reviewed since 2003. I wonder why is wasn't updated for 2005 and 2008?
Or, did I miss a newer article?
Thanks,
Marc
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply