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.
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"
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.
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.
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' |
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') |
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);