Error when querying database

Question

I get the following error when trying to query a data store entity using an expression rule from the process model. (CDT and rule name changed for purpose of forum post) 
 
 ERROR:An error occurred while evaluating expression: APP_CDT_LIST:rule!APP_getAllApplicationsByDateRange(startDate: pv!startDate, endDate:pv!endDate) (Expression evaluation error in rule 'app_getallapplicationsbydaterange' at function a!queryEntity [line 8]: An error occurred while retrieving the data.) (Data Outputs)

mattk · Answer

It turns out that I was running into a data limit for querying the RDBMS. I got around this by implementing batch querying.

Below are two rules, one for creating a list of pagingInfo items that can be passed into the main query expression to compile a list of total records that exist in the database.

/* Query Rule */
with(
local!datasubset: if(
or(
rule!APN_isEmpty(ri!startDate),
rule!APN_isEmpty(ri!endDate)
),
todatasubset({}),
a!queryEntity(
entity: cons!APP_ENTITY_APPLICATIONS,
query: a!query(
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 0),
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "Date",
operator: ">=",
value: ri!startDate
),
a!queryFilter(
field: "Date",
operator: "<=",
value: ri!endDate
)
}
)
)
)
),
local!count: local!datasubset.totalCount,
local!pagingInfoList: rule!APP_generatePagingInfoBatchList(maxBatchSize: 1000, count: local!count),
if(
local!dataSubset.totalCount = 0,
null,
apply(
cast(
'type!{urn:ca:appian:types}APP_CDT',
_
),
a!flatten(
a!forEach(
items: local!pagingInfoList,
expression: {
with(
local!dataSubset: a!queryEntity(
entity: cons!APP_ENTITY_APPLICATIONS,
query: a!query(
pagingInfo: fv!item,
logicalExpression: a!queryLogicalExpression(
operator: "AND",
filters: {
a!queryFilter(
field: "Date",
operator: ">=",
value: ri!startDate
),
a!queryFilter(
field: "Date",
operator: "<=",
value: ri!endDate
)
}
)
)
),
local!dataSubset.data
)
}
)
)
)
)
)

/* Paginging Info for Batching Rule */
load(
local!maxBatchSize: ri!maxBatchSize,
local!count: if(rule!APN_isBlank(ri!count), 0, ri!count),
local!batchCount: roundup(local!count / local!maxBatchSize, 0),
local!numList: enumerate(local!batchCount) + 1,
if(
rule!APN_isBlank(ri!count),
null,
if(
local!count <= local!maxBatchSize,
{a!pagingInfo(1, -1)},
apply(
cast(
typeof(a!pagingInfo()),
_
),
a!forEach(
items: local!numList,
expression: {
a!pagingInfo(
/*fv!item,*/
if(fv!index = 1, fv!item, local!numList[fv!index - 1] * local!maxBatchSize + 1),
local!maxBatchSize
)
}
)
)
)
)
)