CASE logic in SELECT statement

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)

CASE logic in SELECT statement

Author

Topic

cardinco
Starting Member

5 Posts

Posted - 2007-05-02 : 13:53:05

I am trying to set the sort column and sort direction based on a parameter and I am having difficulty doing both. I can do the sort order, but not the direction. Here is what I have:


SELECT *
FROM tblUsers AS o
JOIN  #AllUsers t
ON o.intUserID = t.intUserID
ORDER BY 
    CASE
        WHEN @arg_sort = 'txtLastname' THEN tblUsers.txtLastname
	WHEN @arg_sort = 'txtEmail' THEN tblUsers.txtEmail
	WHEN @arg_sort = 'txtOrganization' THEN tblUsers.txtOrganization
	ELSE txtLastname
    END 
    CASE
	WHEN @arg_sortdir = 'ASC' THEN ASC
	ELSE DESC
    END

This works if I get rid of the second CASE statement, but I want to be able to pass in both the sort order and the sort direction. Any ideas?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-02 : 14:03:55

try


SELECT *
FROM
(
	SELECT 	*,
		sort_order =     
		CASE
	        WHEN @arg_sort = 'txtLastname' THEN tblUsers.txtLastname
		WHEN @arg_sort = 'txtEmail' THEN tblUsers.txtEmail
		WHEN @arg_sort = 'txtOrganization' THEN tblUsers.txtOrganization
		ELSE txtLastname
	    	END 
	FROM tblUsers AS o
	JOIN  #AllUsers t
	ON o.intUserID = t.intUserID
) a
ORDER BY 
    CASE
	WHEN @arg_sortdir = 'ASC' THEN sort_order ASC
	ELSE sort_order DESC
    END

cardinco
Starting Member

5 Posts

Posted - 2007-05-02 : 14:14:30

Thanks for the response. That doesn't work either. It seems that the 'ASC' or 'DESC' keyword needs to exist outside of the CASE statement. If you remove the ASC and DESC from the query it works.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-02 : 14:18:16

not sure if that would work KH...not sure if you can put ASC/DESC dynamically like that..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Alikkin
Starting Member

6 Posts

Posted - 2007-05-02 : 15:58:25

Check out this thread... Helped me with a similar problem. It's an ugly solution... but it works.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942&whichpage=1

cardinco
Starting Member

5 Posts

Posted - 2007-05-02 : 16:37:15

Thanks Alikkin. I actually took the easy way out and did a re-query using my application (ColdFusion).

pootle_flump

1064 Posts

Posted - 2007-05-04 : 04:34:17

I wonder if Jeff saw this and was inspired....
http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-04 : 05:54:11

quote:

Originally posted by khtan

try


SELECT *
FROM
(
	SELECT 	*,
		sort_order =     
		CASE
	        WHEN @arg_sort = 'txtLastname' THEN tblUsers.txtLastname
		WHEN @arg_sort = 'txtEmail' THEN tblUsers.txtEmail
		WHEN @arg_sort = 'txtOrganization' THEN tblUsers.txtOrganization
		ELSE txtLastname
	    	END 
	FROM tblUsers AS o
	JOIN  #AllUsers t
	ON o.intUserID = t.intUserID
) a
ORDER BY 
    CASE
	WHEN @arg_sortdir = 'ASC' THEN sort_order ASC
	ELSE sort_order DESC
    END

I think you can only use literals or columns if you use CASE in Order by clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources