Case Data Query Language (DQL)

DQL is used to define query expressions that can be used as parameters in the findByCriteria methods to search for case references that match the criteria defined by the query expression.

DQL expressions use the syntax:

attributeName operator value

where:

  • attributeName can be specified as a simple nameor as an attribute path, which uses dot notation to navigate around the data model.
  • operator refers to the operators used to search cases. For details, see

    operators.

  • value is the value to match against. It can use constants.
Note: Only those attributes which are made searchable at design time can be used to search cases through DQL. Case State and Case Identifier are by default searchable.

For example:

name = 'Tony Pulis'

Compound Expressions

Simple expressions can be combined with AND to make more complicated expressions. For example, to match records that contain 'London' in the address attribute where the name starts with 'Fred':

address = 'London' AND name = 'Fred'
address = "London" AND name = "Fred" OR name = "Bill"
Note: AND is case-insensitive and the operator precedence applies. For example, OR has a lower precedence than AND and brackets () take the highest precedence.

Reserved Words

The following are reserved words in DQL:

  • and
  • asc
  • between
  • by
  • desc
  • lower
  • not
  • or
  • order
  • size
  • upper

Attribute Paths

An attribute name in a DQL expression can be specified either as a simple name or as an attribute path, which uses dot notation to navigate around the data model. For example:

orders.orderLines.quantity = 1

An attribute path can be:

  • the name of an attribute of the case object

  • the name of a composition/association relationship of the case object

  • the name of an attribute within its containment hierarchy, separated by dots

The path must contain the name of an attribute or relationship, not the label.

If an attribute is multiplicity-many, by default, it matches if any of its values satisfy the condition. Alternatively, '[ALL]' may be placed after the path fragment to indicate that all values must match.

A zero-based index number may be specified after the path fragment in the form '[i]' to require a given value within a list of multiple values to match.

The following table shows a number of example attribute paths and their meanings.

Attribute path Meaning
orderId The orderId attribute.
comments Any value of the multiplicity-many comments attribute.
comments[ALL] All values of the multiplicity-many comments attribute.
comments[0] The first (index zero) value of the multiplicity-many comments attribute.
orderLines The orderLines composition relationship.
orderLines.quantity The quantity attribute of any of the OrderLine objects referred to by orderLines.
orderLines[ALL].quantity The quantity attribute of all of the OrderLine objects referred to by orderLines.
orderLines.orderItem.itemEvent[ALL].description The description attribute of all of the ItemEvent objects referred to by the OrderItem object of any OrderLine object referred to by orderLines. That is, there must be at least one OrderLine where all ItemEvents match.
orderLines[ALL].orderItem.itemEvents.description The description attribute of any of the ItemEvent objects referred to by the OrderItem object of all OrderLine objects referred to by orderLines. That is, every OrderLine must have at least one matching ItemEvent.
orderLines[ALL].orderItem.itemEvents[ALL].description The description attribute of all of the ItemEvent objects referred to by the OrderItem object of all OrderLine objects referred to by orderLines. That is, all ItemEvents must match.
dispatchNote This is a reference to a global object, so only the =null and !=null operators are valid.

Operators

Operator Description Examples
= (Equal to), != (Not equal to), <> (Not equal to) Equal to. Can be used to match wildcard characters.

Can be used to test for null - for example, to test for attributes and associations that have not been set.

Can be used with the following BOM types: Text, ID, Date, Time, Datetimetz, Duration, Integer, Decimal, Enumeration.

name = 'John'
name = 'John'
name = 'John*'
age != 18
name = 'John%'
name = 'Joh_'
name = 'Joh?'
> (Greater than), >= (Greater than or equal to), < (Less than), <= (Less than or equal to) Can be used with the following BOM types: Date, Time, Datetimetz, Duration, Integer, Decimal.  
BETWEEN Test whether a field is between two values (inclusive). The ',' character can be used instead of 'AND'.

NOT BETWEEN can also be used.

Can be used with the following BOM types: Date, Time, Datetimetz, Duration, Integer, Decimal.

quantity between 0 and 20
quantity between 0,20
quantity NOT between 0,20
IN Test if an attribute value matches one of a list of items. If the attribute is a String type, then the values in the IN or NOT IN condition can contain wildcards.

NOT IN can also be used.

Can be used with the following BOM types: Text, ID, URI, Date, Time, Datetimetz, Duration, Integer, Decimal, Boolean, Enumeration.

name IN ('Tony', 'Clint', 'Eric')

Functions

Function Description Notes/Examples
SIZE Find out how many objects there are in a containment.
SIZE(orderLines) = 1
UPPER Convert Text or ID attributes to upper case.
UPPER(postcode) = 'SN%'
LOWER Convert Text or ID attributes to lower case.
LOWER(postcode) ='sn%'

Values

Values in DQL expressions use can use wildcards, constants, and parameters.

Wildcards

Use the '*' or '%' character to match any number of characters.

Use the '_' or '?' character to match any single character.

Use an '\' to escape these characters if you want to actually search for them.

For example:

name = "Fred*"

matches all records that have a name that starts with "Fred".

The string:

address = "*\?*"

matches all records that contain an address that contains a '?' character.

Constants
Type Notes Examples
String

Must be either single-quoted or double-quoted, but not a mixture.

Single quote characters in singlequoted strings, and double quote characters in double-quoted strings, must be escaped using a backslash character ('\').

A backslash character in either type of string must be escaped using a second backslash character.

Characters in a String constant value are case-sensitive. If you require case-insensitive behavior, use the UPPER and LOWER functions. For more information, see Functions.

'bill'
'\'fred'
'\\fred'
"fred"
'bill'
"\"fred\""
"'fred'"
"fred\\bill"
"\\'\""
'\\\'"'
Numeric  
0
-123
123.456
Date Must be specified as:

YYYY-MM-DD

A timezone can be specified by appending the date with either "Z", "+HH:MM" or "-HH:MM".

When doing date range checking you can abbreviate dates, leaving off the non-significant parts.

2013-12-25
2013-12-25Z
2013-12+08:00
2013-05:00

The following expression searches for a date of 2013 or later.

dispatchNote.dateOfDispatch > 2024-07-30
Time Must be specified as:

HH:MM:SS.SSS

When doing time range checking you can abbreviate times, leaving off the non-significant parts.

14:23:56.123

The following expression searches for a time of 09:00:00 or later.

openingTime >= 09:00:56
Datetimetz Must be specified as:

YYYY-MM-DDTHH:MM:SS.SSS

A timezone must be specified by appending the date with either "Z", "+HH:MM" or "-HH:MM".

When doing range checking you can leave off the non-significant parts.

2013-12-25T00:00:01Z
2013-12
2013-12-25T09+05:00
2013-12-25T00:00:01-08:00
Enum Enum names must be quoted with single quotes.
orderLines.orderItem.itemEvents.event = 'RECEIVED'
orderLines.orderItem.itemEvents.event IN ('RECEIVED', 'SHIPPED')
Parameters

Use the process field data in DQL expressions using the following syntax: 

${<path.to.field>}

where <path.to.field> is a data path that references a data field in the process that contains the value that you want to test against in the expression. For example, rather than using a constant value in an expression as:

bpm.caseData.findByCriteria("color = 'Red'","xyz.motors.CarCase", startindex, pagesize);

use a value from your process data as:

bpm.caseData.findByCriteria("color = ${data.OrderDetails.RequiredColor}","xyz.motors.CarCase", startindex, pagesize);