Skip to main content
V
venkateshs595
Participating Frequently
January 17, 2024
Question

Include operator not working in Query Entity with PostgreSQL Database

  • January 17, 2024
  • 7 replies
  • 2 views

Hello all,

We are retrieving a row data from Database using query entity. 

In query filter we have a condition to get a row data.

Example 

a!queryFilter(
field: "name",
operator: "includes",
value: ri!status
)

Database having status value as "awaiting_info" I need to get the row data irrespective of case sensitive like (ri!status= "awaiting_info" or "AWAITING_INFO" or "AWAITING_info" or "awaiting_INFO") .

On the "status" fields in the postgreSQL data base side applied the  case_insensitive COLLATION .But wherever we have applied the case_insensitive COLLATION, those colums are not supporting the  "include" operator from the query entity from Appian expression rule.

Any suggestion on this !!! 

7 replies

rithanir5887
rithanir5887
January 17, 2024

Hello [mention:16cf6901ebc54aaaa636046bc5ec9375:e9ed411860ed4f2ba0265705b8793d05] ,

Have you tried with "in" operator?

V
venkateshs595Author
Participating Frequently
January 17, 2024

Yes "in" operator is working fine.

jayaprakashr0001
jayaprakashr0001
Participating Frequently
January 17, 2024

If you want to use include in the DB it is in lowerCase so in the value try using the lower() function it will work

Value:lower(ri! status) 

V
venkateshs595Author
Participating Frequently
January 17, 2024

I have few use cases to search any text search OR any other operations as mentioned in the example. Ex: (ri!status= "awaiting_info" or "AWAITING_INFO" or "AWAITING_info" or "awaiting_INFO")  . However it is just sample but trying to understand why appian "include" operator not working.

jayaprakashr0001
jayaprakashr0001
Participating Frequently
January 17, 2024

If there is an extra characters like spaces etc in the value "AWAITING_INFO" if your value has

" AWAITING_INFO" then it won't work if you use IN operator it will check the AWAITING_INFO neglect other characters. 

Better to check appian documentation

stefanhelzle0001
stefanhelzle0001
Brainy
January 17, 2024

Found this: https://docs.appian.com/suite/help/23.4/fnc_system_a_queryfilter.html#case-sensitivity-on-record-types-with-data-sync-enabled

But that does not directly answer your question.

I only use constant text values to store and check against such fields. That avoids this issue altogether.

--- A few minutes later ---

Did a small test with MariaDB. The "includes" operator performs a case insensitive search.

Conclusion: Open a support case and report this behavior. I would a similar behavior with PostgreSQL.

    karumurua531442
    karumurua531442
    January 17, 2024

    Hi [mention:16cf6901ebc54aaaa636046bc5ec9375:e9ed411860ed4f2ba0265705b8793d05]  I have did some testing using include operator, It is not an case sensitive search, it is returning  the output irrespective of the case what you have entered in value, As [mention:126a676c85024855948336691b0a7b92:e9ed411860ed4f2ba0265705b8793d05]  said you can open a support for case for this behaviour

    Terms & ConditionsAccessibility statement