Hello !
I am connecting to a view on an external RDBMS via a synonym present on the target system. I've configured a datasource, created a cdt and published the data store successfully.
From my local expression rule I simply am trying to query to fetch data with the last modified date > (ri!dueDate, say 05-NOV-2020). This will be a batch process and I want to fetch around 50-100 records at a time.
[While passing the ri!dueDate in a filter parameter of the a!query() with the operator as '>=' and sort by last modified date in desc, the 5 oldest records were returned (records from 2018) no matter what I tried inside the rule. So I decidedto break this down and start analyzing from scratch. ]
Here's what I get with a descending sort on the last modified date, to fetch the most recent records:
Code:
Result:
A List of variant with 5 items, returning in the correct sort order (latest records from 2020). The time taken for this fetch was 11,160 ms.
If I increase the batchSize to anything greater than 5, I get "Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data."
Now, with the same code as above but without the sort and batchsize = 200, I get all the records from this view which takes about 17,598 ms.
I went through different while trying to understand this -
I've hit a blind spot with this issue. Any ideas or suggestions on what is going on here would be highly appreciated.
Thanks in advance everyone !
Discussion posts and replies are publicly visible
Is it possible that you have defined a primary key on a field that is not unique? All data store entities must have a field that is a primary key, and Appian always assumes that field is unique. If the field ends up not being unique, you can often see unexpected behavior when querying data.
Hi Peter. The primary key is defined on a field that is unique.