Pass Order by value Dynamically in Class Query
Hi,
I want to do ordering dynamically in Query. I mean to say that order by value will not be hard coded in query, it will be passed in parameter.
I have tried to it following way, but it is not working for me. Can you please advise me how can I achieve it?
In below code, Order by :objSearch.SortingField is not working.
Class Query.ExternalUsers Extends %Library.RegisteredObject [ ClassType = "", DdlAllowed, Owner = {_SYSTEM}, Not ProcedureBlock ]
{
Query Search(objSearch As LISSystem.ModelView.UserSearch) As %SQLQuery [ SqlName = ExternalUsersSearch, SqlProc ]
{
select * from (
SELECT TOP ALL FirstName, MiddleName, LastName, Email, UserType
FROM DB.ExternalUsers
WHERE (ISNULL(:objSearch.FirstName, '') = '' OR FirstName LIKE :objSearch.FirstNameSearch)
AND (ISNULL(:objSearch.LastName, '') = '' OR LastName LIKE :objSearch.LastNameSearch)
AND (ISNULL(:objSearch.Email, '') = '' OR Email LIKE :objSearch.EmailSearch)
Order by :objSearch.SortingField
)
WHERE %vid BETWEEN :objSearch.StartIndex AND :objSearch.EndIndex
-- Order by Email
}
}