QueryEntity Difference between = and includes

saravanarajanj

Certified Senior Developer

over 7 years ago

Hi,
We are experiencing the below behavior while using queryEntity.
The queryFilter with "=" operator is returning zero records whereas the filter with "includes" operator returns more records. We are using Oracle database, I ran the query with "=" and "like" operator and able to fetch records in both case.
a!queryFilter(
field:"department",
operator:"=",
value:"Engineering"
)

a!queryFilter(
field:"department",
operator:"includes",
value:"Engineering"
)
Any suggestions/comments are appreciated on this. Thanks!

Discussion posts and replies are publicly visible

0 Nagashankar Nallavalli over 7 years ago

@saravanarajanj please find the below link
forum.appian.com/.../Query_Recipes.html
forum.appian.com/.../System_Functions.html
hope this is helpful to you
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sachin
A Score Level 1
over 7 years ago

@saravanarajanj this behaviour is may be because of additional spaces to the field of the department, just refer the database.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 saravanarajanj
Certified Senior Developer
over 7 years ago

@nagashankarn, thanks. I will go through the links
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 saravanarajanj
Certified Senior Developer
over 7 years ago

@bollurumaheshsachina, thanks, I have tried "=" operator using Oracle query as you suggested. But that is working as expected, returning desired records
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rodgraham
Certified Lead Developer
over 7 years ago

Never seen an issue with this before. Might be worth checking that Appian is connecting to the database you are running the query against and that the data entity is mapped to the correct table and column.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 7 years ago

I recently had this issue with Oracle and the cause was a line feed (char(10)) after the data I was querying. You could notice when copying the result and pasting it into the SQL editor, it pasted a blank line after the data. My resolution was to simply add char(10) to the queryFilter, such as:

a!queryFilter(
field:"department",
operator:"=",
value:"Engineering" & char(10)
)

Might be worth a shot, would be a quick test.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Conor Cahill
Certified Lead Developer
over 7 years ago

if the issue is with leading or trailing newline or other whitespace characters, you could update that column using TRIM or REGEX_REPLACE to get rid of those characters.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 7 years ago

In our case, the view is in a financial database that we do not control - so appending char(10) to the query and cleaning the retrieved data in Appian was the solution and has been working out ok thus far.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 saravanarajanj
Certified Senior Developer
over 7 years ago

@csteward, thank you for your suggestion. I have tried that and still the issue was not resolved.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 saravanarajanj
Certified Senior Developer
over 7 years ago

Hi All, there was a typo error between the department names stored in database. correcting the database value resolved the issue
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel