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
Typically, when we get "Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data.", the problem is either bad SQL for the DB View or no Primary Key.
Hey Bryant, I completely agree. We can rule out the primary key issue because there is one defined. Let me go the SQL route.
Make sure your SQL handles "joins" properly for cases where the child table doesn't have a row or has multiple rows for a row in the parent table.
Sure. Thanks. Since my team does not own the view, I cannot make any changes. Hoping to get edit permissions soon. Working on getting this fixed. I will post updates soon.