New post
Question
· Sep 6, 2019

Pass Order by value Dynamically in Class Query

#Caché #InterSystems IRIS

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

}

}
Discussion (7)0
Log in or sign up to continue
Amit Prajapati · Sep 6, 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
Alexander Koblov · Sep 6, 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 0
Alexander Koblov · Sep 6, 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