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 ) } ) ) ) ) )