Querying Data From an RDBMS

Overview

There are two different ways to retrieve data from an RDBMS in an expression, a!queryEntity() and a!queryRecord. This page describes some of the properties of these queries and details the differences between them.

These queries can be called from any expression, so they can be reused across interface expressions, process models, and record type definitions. Parameters can be used to filter or page the array of data returned.

Comparisons

An a!queryEntity() is an expression function that takes a Data Store Entity and a Query as parameters and executes the query against that Entity. You can easily create simple entity queries using the Query Editor.

An a!queryRecord() is 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.

See the following table for comparisons between a!queryEntity() and a!queryRecord().

  a!queryEntity() a!queryrecord()
Performance Fastest Slowest
Sorting on multiple fields Yes No
Source Data Data Store Entity Process-backed and entity-backed records. Any default filters for the record type are applied.

Operators

See the following table for information on the filter operators that can be used with a!queryEntity() and a!queryRecord.

Case-sensitivity or insensitivity for text comparisons is determined by your RDBMS settings - not a setting configured in Appian.

Operator Description Supported Data Types
= The case-insensitive condition returns True if the field value exactly matches the compared value.
  • If your rule does not require case-insensitivity, consider using the exact() function to improve performance.
  • Boolean, Date, Date and Time, Time, Numbers
    < The condition returns True if the field value is less than the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    <= The condition returns True if the field value is less than or equal to the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    > The condition returns True if the field value is greater than the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    >= The condition returns True if the field value field value is greater than or equal to the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    <> The condition returns True if the field value does not match the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    starts with The condition returns True if the field value begins with the text string listed as your comparison value.
  • The match must be performed based on the literal string provided.

  • Special characters do not have special meaning as wildcards.
  • For example, [starts with: "apple*"] searches for the word apple and the asterisk character (*) rather than any character.

  • If the field value is null, the item will not be returned regardless of the rule input value.
    Text
    ends with The condition returns True if the field value ends with the text string specified as your comparison value.
    If the field value is null, the item will not be returned regardless of the rule input value.
    Text
    includes The condition returns True if the field value contains the string specified as the comparison value.
    If the field value is null, the item will not be returned regardless of the rule input value.
    Text
    in The condition returns True if the field value exactly matches any one of the values in the compared list of values. Boolean and list of Boolean, Date and list of Date, Date and Time and list of Date and Time, Time and list of Time, Numbers and list of Numbers, Text and list of Text
    not in The condition returns True if the field value does not match any one of the values in the compared list of values. Boolean and list of Boolean, Date and list of Date, Date and Time and list of Date and Time, Time and list of Time, Numbers and list of Numbers, Text and list of Text

    Paging Parameter

    You can limit the number of results returned by your query by passing an optional paging parameter of type PagingInfo. This allows you to specify how many items should be returned and the sort of those items.

    When a paging parameter is specified, a value of type DataSubset is returned. Use the a!pagingInfo() function to construct the paging parameter.

    Applying Multiple Sorts to a!queryEntity

    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.

    Other

    The queries you define are limited in how long they wait to return results (10 seconds). This setting can be configured by a system administrator.

    There is also a limit to the amount of memory that can be returned by a single query. This is set by the conf.data.query.memory.limit value. The system will display an error with code APNX-1-4164-024 if this error is reached by a query. Use the paging parameter to return less data (or return data in batches) to avoid the limit.

    See also: Configuring Query Limits

    Best Practices

    It is important that you tailor your queries to provide only desired information, especially when substantial growth in the data-sets queried is expected.

    When designing queries, keep the following considerations in mind:

    Constrain Your Results

    By default, queries do not return a constrained subset of matching data records, unless you configure query conditions and filters and call the query using the PagingInfo parameter.

    • In a typical SELECT query against an RDBMS, a constrained query might be built using the LIMIT clause. In Appian, this is achieved by calling the query with a PagingInfo parameter.
    • Using a query, all records from an entity that match the query conditions are returned.
    • The likely size of the data-set returned needs to be considered when building queries.
    • A larger number of records returned may affect performance of the application.
    • Instead of writing a rule that returns all records or many records, add query conditions and filters that limit the number of results to only those needed.

    Use Additional Criteria

    • Instead of selecting all records less than or greater than a given record, look for additional attributes that you can use to filter the results.

    Use Multiple Conditions

    • Use more than one query condition to reduce the number of records in the result set.

    Problematic Design Example

    The following improper design example illustrates how not to effectively implement queries.

    Given a form that also displays the value of a previous item using a query, the following configuration might be used:

    • A query named GetPrior(currentId) might be used to identify the next numerically-lower item according to the ID value.
    • In this example, currentId is a rule input that holds the id value of the current item.
    • If the query used a query condition of id < currentId, this query would effectively return all matching id values that are numerically lower than the currentId.
    • In this example, only the highest-valued record out of the query's result set is desired.
    • This approach returns much more data than is needed, resulting in a much larger system load than is necessary.

    Best Practice Example

    It is possible to implement a query with an additional rule input to function as a boundary for the attribute that you're filtering on.

    For example, instead of id < currentId, you could implement two query conditions:

    Id < currentId

    — and —

    Id > minimumBoundary

    The query would then use two rule inputs GetPrior(currentId, minimumBoundary). This allows you to nest the query in one or more if statements within your expression.

    For example:

    1
    
    if(length(GetPrior(currentId, currentId-1)) > 0, GetPrior(currentId, currentId-1), GetPrior(currentId, currentId-10))
    
    FEEDBACK