Values
Values in DQL expressions 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*"
will match any records that have a name that starts with "Fred".
The string:
address = "*\?*"
will match any records that contain an address that contains a '?' character.
Constants
| Type | Notes | Examples |
|---|---|---|
| Boolean | Must be written as TRUE or FALSE (case insensitive) | |
| String | Must be either single-quoted or double-quoted, but not a mixture.
Single quote characters in single-quoted 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. |
"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 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 > 2012 |
| Time | Must be specified as:
HH:MM:SS.SSS When doing time range checking you can abbreviate times, leaving off the the non-significant parts. |
14:23:56.123 The following expression searches for a time of 09:00:00 or later. openingTime >= 09:00 |
| Datetime / Datetimetz | Must be specified as:
YYYY-MM-DDTHH:MM:SS.SSS A timezone can (for a Datetime) or must (for a Datetimetz) be specified by appending the date with either "Z", "+HH:MM" or "-HH:MM". When doing range checking you can leave off the 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 either single or double quotes. |
orderLines.orderItem.itemEvents.event = 'RECEIVED'
orderLines.orderItem.itemEvents.event IN ("RECEIVED", "SHIPPED")
|
Parameters
Parameters can be used to supply a constant value at runtime, allowing the same query to be run with different values for the constants.
Specify a parameter name as an alphanumeric identifier, preceded by a colon character:
:parameterID
For example:
name = :customerName
At runtime, parameters can contain multiple values - for example:
name IN :customerList and orders.orderLines.quantity BETWEEN :quantityRange
Use the setQueryParameter() method to set parameter values in the Criteria object. (See Finding Case Objects by Criteria.) The following table shows the different types of parameter value that can be assigned to different attribute types.
| Attribute Type | Parameter Type |
|---|---|
| BigDec | BigDec, BigInt, Double, Int, String |
| BigInt | BigDec, BigInt, Double, Int, String |
| Double | BigDec, BigInt, Double, Int, String |
| Integer | BigDec, BigInt, Double, Int, String |
| Boolean | Bool, String |
| String | BigDec, BigInt, Double, Int, Bool, String, Date, Time, Datetime, Datetimetz, Duration |
| Date | String, Date, Datetime, Datetimetz |
| Time | String, Time |
| Datetime | String, Date, Datetime, Datetimetz |
| Datetimetz | String, Date, Datetime, Datetimetz |
| Duration | String, Duration |
Parameters can have lists of values assigned to them. (The IN and BETWEEN functions only have single parameters - when used with BETWEEN the parameter must have two values.)
Parameter values can be set to null to find attributes or associations that are not set.