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
Update on this : Problem does not seem to be with the database schema view or synonym. I verified by creating a view on the service account directly and tried accessing the same from Appian. I still get an error, the same one as above.
This view I am querying can have up to a million records. I only give a batch size of 10 or 20 while testing. No luck.
So I created a test table on the same service account and inserted about 10 rows to rule out if it was timing out due to the data size and this seems to be the reason. The 10 records were e retrieved. Checked the query time out and it is set to around 30 s in the test environments.
Is there another way Appian can handle this ? Batching does not seem to be helping. The view is pretty straightforward too. Does anyone have any thoughts on this?
Sujatha, did you get this figured out? I am having they same issue. I'm pretty sure my view is correct and matches the CDT I created but the query is getting an error.
I figured out my issue by removing fields that were not straight data for data to find my issue. I was creating an integer field with a coalesce( function and inserting a ' ' if there was no data for the field which an integer doesn't like spaces so I was getting an error in the query in the expression rule but not in the SQL. I changed the ' ' to null and the expression rule is able to read the view.
Hi lesliec,
Glad you figured out your issue. I've tried to create a table in my local database and loaded into it the same data the external view returned around 93000 records. I still get the same error. Indexes are set on the columns I use to filter and there is a primary key set as well.
I'm beginning to run out of scenarios to debug and thinking it could be something to do with the data. Any thoughts ?
just be curious: could you find a solution?I read the other comments and my first impresison is that i am with mike here. More a view issue than an appian issue.
Update : Issue was with the data in one of the columns. The id column that I had defined as the primary key in my cdt data type number, it's corresponding column in the view /table has values exceeding the upper limit of Integer (Number) values. It contains values >9 digits.