Order By Data Set - 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 2008 Forums

Analysis Server and Reporting Services (2008)

Order By Data Set

Author

Topic

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2012-04-23 : 11:24:15

I'm working on a report that the user will have two different sorts to select from.

There are two sort selects for the user and each selection is ordered by several fields.

In my dataset, I have the Order By statement as follows:

ORDER BY CASE
WHEN @SortBy = 'A' THEN [CASE ID], [CONTACT ALPHA], [SCHEDULED PRIORITY], [CONTACT ALPHA]
WHEN @SortBy = 'P' THEN [CASE ID], [SCHEDULED PRIORITY], [CONTACT ALPHA]
END

This is not working for me. I keep getting prompted to enter values when I click on OK. Usually when that happens, something is wrong.

Can someone spot where I went wrong, which I'm probably overlooking.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 11:57:19

Put only one column in one case expression. And those that you do put in a case expression should all be the same data type. In your case, you could do the following:

ORDER BY
	[CASE ID],
	CASE
		WHEN @SortBy = 'A' THEN [CONTACT ALPHA] 
	END, 
	[SCHEDULED PRIORITY], 
	[CONTACT ALPHA]
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:09:44

Actually you both case branches look same . only difference i find is one branch you're using column [CONTACT ALPHA] twice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2012-04-23 : 14:22:21

Thanks guys. I went this route:

ORDER BY [CASE ID],

CASE
WHEN @SortBy = 'A' THEN [CONTACT ALPHA]
WHEN @SortBy = 'P' THEN [SCHEDULED PRIORITY]
END,

CASE
WHEN @SortBy = 'A' THEN [SCHEDULED PRIORITY]
WHEN @SortBy = 'P' THEN [CONTACT ALPHA]
END,
etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 14:49:33

if this is for reporting requirement you dont need to do this in t-sql. Most reporting tools have this feature built into them called 'Interactive sorting'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources