Possible Case Statement limitation? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Possible Case Statement limitation?

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2001-11-29 : 18:06:00

The problem is that I wish to do a select and order by a user selected column.

The following query works fine and orders by 'total' descending:

SELECT COUNT(*) AS 'total'
,computer
FROM TBL_Symantec_Log_Entries
WHERE event = 5 AND
EventTime > '11/18/01'
GROUP BY computer
ORDER BY 'total' desc

However, the following query does not work correctly and always orders by computer ascending:
DECLARE @col AS varchar(15)

SELECT @col = 'total'

SELECT COUNT(*) AS 'total'
,computer
FROM TBL_Symantec_Log_Entries
WHERE event = 5 AND
EventTime > '11/18/01'
GROUP BY computer
ORDER BY CASE @col
WHEN 'total' THEN 'total'
ELSE computer
END
DESC

It seems as though this should be possible. The problem seems to be related to referencing the count(*) column as 'total'. If I use this same query with two "real" table columns it works fine. Any ideas?

Thanks,
Chris

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-11-29 : 18:25:59

SELECT @col = 'total'

SELECT COUNT(*) AS total
,computer
FROM TBL_Symantec_Log_Entries
WHERE event = 5 AND
EventTime > '11/18/01'
GROUP BY computer
ORDER BY CASE when @col <> 'total' THEN computer end desc,
total desc

In your query, the first criteria will always be constant ('total') and will not have any implication on the ordering

mono
Starting Member

36 Posts

Posted - 2001-11-30 : 08:03:37

cbrinson is right when he states:

quote:
It seems as though this should be possible. The problem seems to be related to referencing the count(*) column as 'total'. If I use this same query with two "real" table columns it works fine. Any ideas?

There is possible confusion between 'total' the string and 'total' the column. I'd use a different token e.g. a named constant for the @col variable. So you might have:

DECLARE @TOTAL _COL
DECLARE @COMPUTER_COL
...
CASE WHEN @COL = @TOTAL THEN [TOTAL] ELSE [COMPUTER]...

The reason it is failing (I *think*) if is that
...WHEN 'total' THEN 'total'...
tells it to use the string 'total' not the column 'total'. Write it:
...WHEN 'total' THEN total...
or even better:
...WHEN 'total' THEN [total]...
to make it clear to us humans as well as to the parser that we are referring to a column not a string.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-01 : 00:32:52

>> cbrinson is right when he states: It seems as though this should be possible.
He is right it is possible and the server does it correctly
consider rows
1
2
3

order these by 'total' then each row has the same literal 'total' so this has no effect on the order.
There may be a bit of confusion caused by including the output column name in single quotes which is incorrect as it is an identifier not a literal.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources