Querying Data From an RDBMS

There are several different ways to retrieve data from an RDBMS in an expression. This page will detail their similarities and differences. All three of these methods can be used in Interface expressions, process models, and record type definitions.

Query Rules: A rule that contains a defined query against a single data store entity.

a!queryEntity(): An expression function that takes a Data Store Entity and a Query as parameters and executes the query against that Entity.

queryrecord(): An expression function that takes a process-backed or entity-backed Record Type and a Query as parameters and executes the query against that Record Type.

Query Rules a!queryEntity() queryrecord()
Performance Medium Fastest Slowest
Columns Retrieves all columns Retrieves only the specified columns Retrieves only the specified columns
Aggregations None COUNT, SUM, AVG, MIN, MAX COUNT, SUM, AVG, MIN, MAX
Can Retrieve Unique Values No Yes Yes
"Between" Filter No Yes Yes
"Not Includes" Filter No Yes Yes
"Not Starts With" Filter No Yes Yes
"Not Ends With" Filter No Yes Yes
"Is Null" Filter No Yes Yes
"Not Null" Filter No Yes Yes
OR Filters No Yes Yes
Nested Filters No Yes Yes
Sorting on a list field Yes No No
Sorting on multiple fields Yes Yes No
Return Type Returns either an Array of CDTs or a DataSubset of CDTs Always returns a DataSubset of Dictionaries Always returns a DataSubset of Dictionaries
Source Data Data Store Entity Data Store Entity Process-backed and entity-backed records. Any default filters for the record type are applied.
The following rules apply to a!queryEntity when applying multiple sorts:
  • Fields will be sorted in the order in which the sort is provided. For example, if sorting on column A and B, the entity will be sorted by A, then B.
  • When using selection, you can sort on any field on the entity, including those fields that are not part of the selection.
  • When using aggregation, you can only sort on fields that are selected. Otherwise, an error will be returned.
FEEDBACK