Page Comparison

...

The query language

...

The query language ressembles the SQL language and allows powerful queries. A typical example would be:

...

General features

=

==

Compare field with a value. You can use both "=" and "==". These and other operators can be used with most field types.

Code Block
{reference} = "1223-82" {id} = 10234

Use double quotes with strings.

Inside a string literal, a double quote is written as two consecutive double quotes:

Code Block
{reference} = "Reference ""123"""

>, <

>=, <=

To compare numeric field values and dates.

Code Block
{count} >= 100

!=

<>

Not equal operators. Both have the same function.

AND

&&

Boolean "and" to combine conditions.

Code Block
{count} >= 100 && {count} < 1000 {count} >= 100 AND {count} < 1000

OR

||

Code Block
{count} < 100 \|\| {count} > 1000 {count} < 100 OR {count} > 1000

!=

Not equal.

Code Block
{count} != 100 {reference} != "alpha"

( )

Brackets can be used to combine multiple conditions:

Code Block
({count} == 100 OR {count} == 101) AND {status} == 10

!

NOT

Negation:

Code Block
(NOT {count} == 100) OR {count} == 101 is equal to: {count} != 100 OR {count} == 101

x ? y : z

Evaluates y if x is true, evaluates z if x is false.

Code Block
{count} >= ({status} = 1 ? 100 : 1000)

null

Literal to evaluate if a field is null or not:

Code Block
{deadline} == null NOT {deadline} == null

Numbers

+ - * /

Numeric operators.

Code Block
({words} * {document} > 1000)

Decimals

Decimal fields use the optional '.' (dot) decimal point:

Code Block
{vat} > 18.5

Strings

.StartsWith

.EndsWith

.Contains

Various operators to search string fields by prefix, suffix or infix.

Use NOT to negate.

Code Block
{reference}.StartsWith("123") {reference}.EndsWith("123") {reference}.Contains("123") NOT {reference}.Contains("123")

.Length

To query by string length:

Code Block
{reference}.Length < 10

Dates

You can use the DateTime object to compare dates.

To compare a date:

Code Block
{deadline} >= DateTime(2007, 1, 1)

Or with hours, minutes and seconds:

Code Block
{deadline} < DateTime(2007, 1, 1, 10, 30, 0)

...

Basic

Exact numeric match:

Code Block
{words}.Matches(100) {words}.Matches(100, "=") // "=" is the default operator {words}.Matches(100, "!=") // Different from 100

Range match:

Code Block
{words}.Matches(100, ">=", 500, "<=")

The operators can be any of: >, <, >=, <=, =, !=

Any of

This construct matches if the field matches any one of the pipe separated values:

Code Block
{status}.Matches("1,2,5", "anyof")

Null values

Find null values with fields that are nullable:

Code Block
{words}.Matches(null) {words}.Matches(null, "!=")

...

Decimals filtering

Basic

Like with numeric fields, you can do different comparisonsSummary

You can use all the features also available with number filters.

Please always use the "." (dot) character for the decimal point. The decimal point is optional.

Examples:

Code Block
{deadlinevat}.Matches("2018", ">="19.5) {deadlinevat}.Matches("2018-10-10", ">=") 18, ">") // "=" is the default operator

Date filtering

Basic

Like with numeric fields, you can do different comparisons:

Code Block
{deadline}.Matches("2018-10-10", ">=", "2019-2-2", "<") {deadline}.Matches("2018-10-10", ">=") {deadline}.Matches("2018-10-10", ">=", "2019-2-2", "<") {deadline}.Matches("2018-12-25:10:45:20Z", "!=")

Important:

Dates are always interpreted as UTC.
Partial date/times are automatically expanded. Example: "2018" is converted to 2018-01-01 00:00
See below for permitted date formats

Date formats

Formats that are supported are:

- 2018
- 2018-10
- 2018-10-25
- 2018-10-25 22:30:00 or 2018-10-25T22:30:00 or 2018-10-25T22:30:00Z
- 2018-10-25 22:30:00.123 (append fraction of seconds) + variants as before
- Full round trip format: 2009-06-15T13:45:30.0000000Z

Note: Partial date/times are automatically expanded. Example: "2018" is converted to 2018-01-01 00:00

Null values

Find null values with fields that are nullable:

Code Block
{deadline}.Matches(null) {words}.Matches(null, "!=")

Filter day or month

All deadlines on a day (UTC):

Code Block
{deadline}.Matches("2018-10-25", ">=") AND {deadline}.Matches("2018-10-26", "<")

All deadlines in a month (UTC):

Code Block
{deadline}.Matches("2018-10", ">=") AND {deadline}.Matches("2018-11", "<")

Date offsets

Sometimes it is easier to filter by a date offset.

For example: Find jobs with a deadline of tomorrow. This can be expressed with "1d". More examples:

"1y 2m" : 1 year and 2 months into the future
"-2d": 2 days into the past
"+4 M": 4 minutes into future
"24H 30M 20S": 24 hours, 30 minutes and 20 seconds into the future

You can thus use one or more combinations of:

y, m, d: Year, Month, Day
H, M, S: Hours, Minutes, Seconds

Deadline within +/- 6 hours:

Code Block
{deadline}.Matches("-6H", ">=") AND {deadline}.Matches("+6H", "<=")

All objects created since 1 year and 6 months:

Code Block
{created}.Matches("-1y 6m", ">=")

Date offset filtering

Date offsets

Sometimes it is easier to filter by a date offset.

For example: Find jobs with a deadline of tomorrow. This can be expressed with "1d". More examples:

"1y 2m" : 1 year and 2 months into the future
"-2d": 2 days into the past
"+4 M": 4 minutes into future
"24H 30M 20S": 24 hours, 30 minutes and 20 seconds into the future

Deadline within +/- 6 hours:

Code Block
{deadline}.Matches("-6H", ">=") AND {deadline}.Matches("+6H", "<=")

All objects created since 1 year and 6 months:

Code Block
{created}.Matches("-1y 6m", ">=")

Units

You can thus use one or more combinations of:

y, m, d: Year, Month, Day
H, M, S: Hours, Minutes, Seconds

You can prefix offset with "-" for dates into the past: -1y stands for one year ago.

You can prefix with optional "+" for dates into the future: +1y (or 1y) stands for one year from now.

Boolean filtering

Basic

Valid examples:

Code Block
{mybool}.Matches(true) {mybool}.Matches(false) {mybool}.Matches(null) // Field is not set (null) {mybool}.Matches(null, "!=") // Fiield is set and either true or false

Permitted operators are: "=" and "!=".

...

Versions Compared

Old Version 30

New Version Current

Key

Decimals filtering

Date filtering

Date offset filtering

Boolean filtering