Skip to main content
venkateshs595
Participating Frequently
Jan 17, 2024 2:24 PM UTC
Question

Include operator not working in Query Entity with PostgreSQL Database

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
Participating Frequently
Jan 17, 2024 2:34 PM UTC

Hello [mention:16cf6901ebc54aaaa636046bc5ec9375:e9ed411860ed4f2ba0265705b8793d05] ,

Have you tried with "in" operator?

venkateshs595Author
Participating Frequently
Jan 17, 2024 2:35 PM UTC

Yes "in" operator is working fine.

jayaprakashr0001
jayaprakashr0001
Participating Frequently
Jan 17, 2024 2:36 PM UTC

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) 

venkateshs595Author
Participating Frequently
Jan 17, 2024 2:40 PM UTC

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
Jan 17, 2024 2:43 PM UTC

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
Jan 17, 2024 2:37 PM UTC

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
    Participating Frequently
    Jan 17, 2024 3:19 PM UTC

    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