Pass Order by value Dynamically in Class Query

Answer by Alexander Koblov 6 September 2019

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:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like :objSearch.FirstName
ORDER BY :objSearch.SortingField

This query is compiled as:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like ?
ORDER BY ?

Then during runtime you supply values as follows:
objSearch.FirstName = 'A%'
objSearch.SortingField = 'FirstName'

And query is executed as follows:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like 'A%'
ORDER BY 'FirstName'

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:

 Order by CASE :objSearch.SortingField
          WHEN 'FirstName' THEN FirstName
          WHEN 'MiddleName' THEN MiddleName
          ...
          END

Although such generic queries makes SQL Query Analyzer unable to reason what plan is better to use for this query.

+ 1 1

Accepted answer

Comment by Amit Prajapati 6 September 2019

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.

select * from (

SELECT TOP ALL FirstName, MiddleName, LastName, Email, UserType

FROM LISDB.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

Order by

CASE :objSearch.SortingField

WHEN 'FirstName' THEN FirstName

WHEN 'MiddleName' THEN MiddleName

END

CASE :objSearch.IsDecending

WHEN 1 THEN DESC

WHEN 0 THEN ASC

END

)

WHERE %vid BETWEEN :objSearch.StartIndex AND :objSearch.EndIndex

0 0

Comment by Alexander Koblov 6 September 2019

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.

ClassMethod Search(
    Name As %String = "",
    SSN As %String = "",
    Title As %String = "",
    SortingField As %String = "",
    StartIndex As %String = "") As %Integer [ SqlName = ExternalUsersSearch, SqlProc ]
{
 set query = "select Name, Title, SSN from Sample.Employee WHERE 1=1 "
 kill args

 if Name'="" {
     set args($I(args)) = Name
     set query = query _ "AND Name like ? "
 }

 if SSN'="" {
     set args($I(args)) = SSN
     set query = query _ "AND SSN like ? "
 }

 if Title'="" {
     set args($I(args)) = Title
     set query = query _ "AND Title like ? "
 }

 set AllowedFieldsToOrderBy = $LB("Name", "SSN", "Title")
 if $ListFind(AllowedFieldsToOrderBy, SortingField) {
    set query = query _ " ORDER BY " _ SortingField

    if StartIndex = 1 {
        set query = query _ " DESC"
    } else {
        set query = query _ " ASC"
    }
 }

 set rs = ##class(%SQL.Statement).%ExecDirect(,query,args...)

 #dim %sqlcontext As %SQLProcContext
 if rs.%SQLCODE >=0 {
     do %sqlcontext.AddResultSet(rs)
 } else { // pass errors to the caller
     set %sqlcontext.%SQLCODE = rs.%SQLCODE
     set %sqlcontext.%Message = rs.%Message
 }
 quit 1
}

0 0

Comment by Amit Prajapati 6 September 2019

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.

0 0

Comment by Alexander Koblov 6 September 2019

Return statement is

do %sqlcontext.AddResultSet(rs)

When you call this stored procedure it returns resultset

For example:

call CMT.ExternalUsersSearch('%in%',,,'Title')

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...

+ 1 1

Pass Order by value Dynamically in Class Query

Primary tabs

Answers