Dynamic Sort Question
-
I have a T-SQL problem that I'm hoping someone can help me with.
I am displaying a sortable table that looks like this:
ER Council Members
Volunteer Status
Other Councils
Name OPP VOL MBR V2020 RRG QOL YEL Mr. Charles Cherry Sr. (Co-Chair) X X X Donald Clem X Mr. Andrew McClintick X X Michael McLinden X Joseph Hang Khan Mung X Alan Pennington X X The table displays a user name, their volunteer status and membership in other councils besides the one being listed. Each column is sortable.
There are two tables - Users and UsersPlansCouncils. The users table is self-explanatory. The UsersPlansCouncils table contains one or more rows for each member, one for each council they are in. For each council, it contains their volunteer status. The relevant parts of the table definition looks like this:
[pkID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[fkUserID] [int] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_fkUserID] DEFAULT (0),
[fkCouncilID] [int] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_fkCouncilID] DEFAULT (0),
[OPP] [bit] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_OPP] DEFAULT (0),
[Volunteer] [bit] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_Volunteer] DEFAULT (0),
[Member] [bit] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_Member] DEFAULT (0),
[IsCoChair] [bit] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_IsCoChair] DEFAULT (0),
[IsActive] [bit] NOT NULL CONSTRAINT [DF_UsersPlansCouncils_IsActive] DEFAULT (1)
Here is the query that I came up with to display and sort the information (the part I need help with is in blue):
ALTER PROCEDURE _procSelectNamesByCouncil
(
@CouncilId INT,
@SortField VARCHAR(20)=''
)
AS
SET NOCOUNT ON
SELECT
u.pkID as UserID,
u.Salutation,
u.FirstName,
u.LastName,
u.Suffix,
u.Email AS Email,
m.fkCouncilID as CouncilID,
m.OPP,
m.Volunteer,
m.Member,
m.IsCoChair,
m.DateAssigned,
m.DateModified,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=1) THEN 'X' ELSE ''
END AS V2020,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=2) THEN 'X' ELSE ''
END AS RRG,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=3) THEN 'X' ELSE ''
END AS QOL,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=4) THEN 'X' ELSE ''
END AS ER,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=2) THEN 'X' ELSE ''
END AS YEL
FROM
Users u INNER JOIN UsersPlansCouncils m ON u.pkID = m.fkUserID
WHERE m.fkCouncilID = @CouncilId
ORDER BY
m.IsCoChair DESC,
CASE
WHEN @SortField='OPP' THEN m.OPP
WHEN @SortField='VOL' THEN m.Volunteer
WHEN @SortField='MBR' THEN m.Member
--WHEN @SortField='V2020' THEN V2020
--WHEN @SortField='RRG' THEN RRG
--WHEN @SortField='QOL' THEN QOL
--WHEN @SortField='ER' THEN ER
--WHEN @SortField='YEL' THEN YEL
END DESC,
u.LastName
The selection gives me the correct information, but when I try to add the derived columns to the ORDER BY, I get an "Invalid Column Name" error.
Can someone help me with this? Many, many thanks in advance!
-
You can't reference output column aliases in the ORDER BY.
Eg:
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=1) THEN 'X' ELSE ''
END AS V2020
This entire expression is aliased as "V2020". You're tyring to use "V2020" as an order by, but it is not a column (hence the error) but an alias. You need to replicate the expression. (Or have the select portion as a virtual table, then select from the virtual table and have the aliases available to ORDER BY).
-
Thanks for the quick response!
I guess I thought they were "virtual columns" because they were showing up as columns in Query Analyzer.
"You need to replicate the expression."
So, how does one go about "replicating" the expression? I don't understand what you mean by that.
"(Or have the select portion as a virtual table, then select from the virtual table and have the aliases available to ORDER BY)."
Also, how does one go about doing that? Can you give me a quick example?
THANKS!!!
-
>>So, how does one go about "replicating" the expression? I don't understand what you mean by that.
Instead of ORDER BY V2020, you need to ORDER BY {expression that is V2020}
i.e.
ORDER BY CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=1) THEN 'X' ELSE ''
END
Which gets messy and error prone if you're later maintaining the code and changeteh expression in the SELECT and forget to make the same change in the ORDER BY.
Re-writing as a SELECT from a virtual table:
SELECT * FROM
( -- Virtual Table between parentheses
SELECT
u.pkID as UserID,
u.Salutation,
u.FirstName,
u.LastName,
u.Suffix,
u.Email AS Email,
m.fkCouncilID as CouncilID,
m.OPP,
m.Volunteer,
m.Member,
m.IsCoChair,
m.DateAssigned,
m.DateModified,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=1) THEN 'X' ELSE ''
END AS V2020,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=2) THEN 'X' ELSE ''
END AS RRG,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=3) THEN 'X' ELSE ''
END AS QOL,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=4) THEN 'X' ELSE ''
END AS ER,
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=2) THEN 'X' ELSE ''
END AS YEL
FROM
Users u INNER JOIN UsersPlansCouncils m ON u.pkID = m.fkUserID
WHERE m.fkCouncilID = @CouncilId
) -- End of virtual table
ORDER BY
IsCoChair DESC,
CASE
WHEN @SortField='OPP' THEN OPP
WHEN @SortField='VOL' THEN Volunteer
WHEN @SortField='MBR' THEN Member
WHEN @SortField='V2020' THEN V2020 -- Can reference by alias now
WHEN @SortField='RRG' THEN RRG
WHEN @SortField='QOL' THEN QOL
WHEN @SortField='ER' THEN ER
WHEN @SortField='YEL' THEN YEL
END DESC,
LastName
-
If you get an error near the order by, it means that you must give an alias name to the table :
WHERE m.fkCouncilID = @CouncilId
) dtYourNameOfChoice -- End of virtual table
ORDER BY
-
YEAH!!! You guys are AWESOME!
All I had to do to make your example work was to change 'X' to 1 or 0:
CASE WHEN EXISTS(SELECT pkID FROM UsersPlansCouncils WHERE UsersPlansCouncils.fkUserID=u.pkID AND UsersPlansCouncils.fkCouncilID=1) THEN 1 ELSE 0
END AS V2020,
THANKS!!!!
-
Good eye, Remi
- I always forget the alias after the closing parenthesis on the vt. -
I've stopped counting how many times I forgot that
. -
Or if your data set is small and you are using ADO you could sort in the recordset itself using the SORT method which might be a bit easier to code.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply