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:

For example: 

name = "Tony Pulis"

Compound Expressions

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

address = "London" AND name = "Fred" OR name = "Bill"

Normal operator precedence applies (AND is highest and OR is lowest) and can be overridden with the use of brackets. The example just given would be evaluated as: 

((address = "London") AND (name = "Fred")) OR (name = "Bill")

AND and OR are case-insensitive.

When a compound expression refers to attributes that have multiplicity in their path, then the conditions are required to match the same object. For example, consider the following classes:

The following query requires a single orderline to match both conditions - it would not match if one orderline satisfies the first condition and another satisfies the second: 

orderLines.quantity > 100 and orderLines.partNum = ‘1350CNW’

This query would not, therefore, match an order with the following orderLines:

quantity partNum
150 ADR460
75 1350CNW

If you want to allow conditions to match different members, you can assign tags to the part of the attribute path that has multiplicity. Tags consist of a dollar sign followed by a letter, optionally followed by further numbers and letters. Assigning different tags to each condition removes the need for them to match the same object, so the following query would match the order described above. 

orderLines[$a].quantity > 100 and orderLines[$b].partNum = ‘1350CNW’

Similarly, to find an order with one orderline for 150 x ADR460 and another for 75 x 1350CNW, the following query could be used: 

orderLines[$x].quantity = 150 and orderLines[$x].partNum = ‘ADR460’ and
     orderLines[$y].quantity = 75 and orderLines[$y].partNum = ‘1350CNW’

Sort Order

You can add an ORDER BY clause to the end of the DQL statement to specify what order the records should be returned in. The format of the ORDER BY clause is:

[ORDER BY column ASC|DESC[, column ASC|DESC][..]]

Each column specification is a multiplicity-single attribute path followed by one of the keywords ASC or DESC.

For example, to find all the customers with the name "Blogs" and sort by name then address: 

name = "%Blogs" ORDER BY name ASC, address asc

Reserved Words

The following are reserved words in DQL:
  • and
  • asc
  • between
  • by
  • desc
  • lower
  • not
  • of
  • or
  • order
  • size
  • type
  • upper

You can use a reserved word in a query expression if you wrap the reserved word in curly brackets.

For example, if you have an attribute called "type" and you want to find all objects whose "type" attribute has a value of 1, you must wrap the reserved word in curly brackets, as shown below. 
{type} = 1
Similarly, if you have a parameter called "type", you must wrap the reserved word in curly brackets, as shown below. 
myAttribute = :{type}
If an attribute name has a suffix such as [ALL] or [1], then the curly braces go around the name only. For example: 
{type}[ALL] = 1