Dynamic Sort Question
-
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 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply