CASE in ORDER BY causes concatenation to break

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)

CASE in ORDER BY causes concatenation to break

Author

Topic

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-09-26 : 14:42:08

Take the following code example. It should run directly in QA:

DECLARE @mytable TABLE
(FirstName VARCHAR(10)
,LastName  VARCHAR(15)
)

DECLARE @NameList VARCHAR(500)
DECLARE @SortOrder SMALLINT

SET @SortOrder = 1

INSERT @mytable VALUES('Ralph','Johnson')
INSERT @mytable VALUES('Suzie','Williams')
INSERT @mytable VALUES('Jerry','Foster')
INSERT @mytable VALUES('Robin','Griffin')
INSERT @mytable VALUES('Todd','Smith')

SELECT @NameList = ISNULL( @NameList + ',', '' ) + FirstName
FROM @mytable
ORDER BY FirstName
/* Use this ORDER BY to see the weirdness
ORDER BY CASE WHEN @SortOrder = 1 THEN FirstName ELSE LastName END
*/

SELECT @NameList

As written it works great - I get a comma-delimited list of first names. However, if I use the ORDER BY that is commented out instead, @NameList only includes the last value. I can't figure out why.

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-26 : 15:27:54

I'm at the library, so I don't have anything to test with...

But,

Try settin @Namelist to an empty string first

SET @NameList = ''

Then your select...

[shot in the dark]Hope no one got hurt[/shot in the dark]

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

dsdeming

479 Posts

Posted - 2003-09-26 : 15:36:35

One way around it is to use a derived table:

set nocount on
DECLARE @mytable TABLE
(FirstName VARCHAR(10)
,LastName VARCHAR(15)
)

DECLARE @NameList VARCHAR(500)
DECLARE @SortOrder SMALLINT

SET @SortOrder = 1

INSERT @mytable VALUES('Ralph','Johnson')
INSERT @mytable VALUES('Suzie','Williams')
INSERT @mytable VALUES('Jerry','Foster')
INSERT @mytable VALUES('Robin','Griffin')
INSERT @mytable VALUES('Todd','Smith')

SELECT @NameList = ISNULL( @NameList + ',', '' ) + a.FirstName
FROM ( SELECT TOP 100 PERCENT FirstName FROM @mytable ORDER BY CASE WHEN @SortOrder = 1 THEN FirstName ELSE LastName END ) a

SELECT @NameList

Dennis

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2003-09-26 : 15:51:45

quote:
Originally posted by dsdeming

One way around it is to use a derived table:

That works great, thanks!

Now, if I only knew WHY it didn't work in the first place. Stuff like that bugs me...

3P

==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources