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
}
}
From docs.
see: Host Variables
You are using instead object property references.==> objSearch.StartIndex, .....
Load your properties into local variables and it should work
object property is working fine in Query expect objSearch.SortingField
As per your suggestion, I have changed the query and it is working fine.
But I have to implement direction of sorting dynamically and it is not working. Please give your expert advise.
In below code, bold highlighted not working and give me compile time error.
Not possible to do this in Query. You need to use dynamic SQL.
Symbol ":" is used to indicate host variables. Host variables are treated as expressions, not as identifiers.
During query compilation host variables are replaced with placeholders.
Consider query:
This query is compiled as:
Then during runtime you supply values as follows:
objSearch.FirstName = 'A%'
objSearch.SortingField = 'FirstName'
And query is executed as follows:
Notice 'FirstName' is in quotes in ORDER BY. So you sort by literal string. That is doing nothing.
What you can do is to use expression like:
Although such generic queries makes SQL Query Analyzer unable to reason what plan is better to use for this query.
CASE statement expects expression after THEN. DESC or ASC are not expressions. That's why you are getting syntax error.
So you need to supply some expression, ordering by which would mean reverse ordering by FirstName.
I don't know how to do this.
I would do sorting on the client or use dynamic SQL to create the query. As below for example.
Important! Nowhere I concatenate parameters of the stored procedure with the query to avoid SQL injections. Only SortingField is concatenated after checking that it has approved value.
Thanks for your suggestion with example code.
Can you please advise how we get the data using this Search Method, because you did not write any return statement.
Return statement is
do %sqlcontext.AddResultSet(rs)
When you call this stored procedure it returns resultset
For example:
If you call this stored procedure via ODBC / JDBC you need to add ReturnResultsets to the method definition:
https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?K...
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue