Operators for WHERE clauses

The following list provides an overview of the operators that are allowed in Alfabet queries for different data types.

  • If you define the Alfabet query with the Alfabet Query Builder, only the allowed operators will be displayed in the menu when you click the property defined in a WHERE clause. For some special properties (for example the REFSTR or the MANDATEMASK property), you can use only a subset of the operators specified in the table below. The table lists the default for the use of the operators per data type.
  • The range of allowed operators for a data type includes combinations that are only relevant for special contexts. Not all allowed combinations of data type or operator may be meaningful for the context you are working with.
  • WHERE clauses can only be built on properties of the data type ReferenceArray if the object class property's attribute Reference Support is set to True in the Alfabet meta-model. For the rare case that Reference Support is set to False , the property cannot be used in Alfabet queries.
Operator Description For Data Type Example
LIKE Similar to a defined string.

String

StringArray

  • Application.Name LIKE 'A%'

(finds all applications whose names begin with "A")
NOT LIKE Different to a defined string.

String

  • Application.Name LIKE 'A%'

(finds all applications whose names do not begin with "A")
CONTAINS Similar to.

NOTE: CONTAINS differs from LIKE for the generation of filter fields in reports based on Alfabet queries.

String

StringArray

Reference

ReferenceArray (with the Reference Support attribute set to True)
  • Application.Name CONTAINS 'A%'

(finds all applications whose names begin with "A")

For properties of the type StringArray , only entries exactly matching the defined string will be found. Therefore, if you want to find entries containing only one specific selection, enter ’Selection’. To find entries containing one specific selection and optionally other selections, enter ’% Selection% ’. If you want to find entries all with at least two defined selections, enter ’% Selection1% Selection2%’. The selections must be listed in the order they are listed in the enumeration.
CONTAINSOR

The object class property value is to be found in the values of a list. The list is enclosed in inverted commas and the individual values are separated by a comma.

CONTAINSOR can be used to create multi-select combo-box filters.

String

StringArray

Reference

ReferenceArray (with Reference Support set to True)
  • Person.Name CONTAINSOR ’Miller,Young,Singer’

(finds all users with the name Miller, Young or Singer)

In the Alfabet Query Builder, the comma-separated list of values must be defined in the dialog box for entering values without the inverted commas.
IN ()

The object class property value is to be found in the values of a list. The list is enclosed in a parenthesis (). The individual values are comma-separated and each written into inverted commas.

Integer

Real

Reference

The operator IN can also be used to compare the REFSTR property of an object class with a list of values.
  • Project.ReportProgress IN ('10','20',30')

(finds all projects with a report progress of 10, 20, or 30)

In the Alfabet Query Builder, the list of values must be defined in the dialog box for entering values without the parenthesis, separating commas and inverted comma. Instead, values must be separated with a whitespace. The list of integers in the example above must be written into the field as:

  • 10 20 30
NOT IN ()

The object class property value is not to be found in the values of a list. The list is enclosed in a parenthesis (). The individual values are comma-separated and each written into inverted commas

Integer

Real

Reference

The operator IN can also be used to compare the REFSTR property of an object class with a list of values.
  • Project.ReportProgress NOT IN ('10','20','30')

(finds all projects with a report progress of 10, 20, or 30)

In the Alfabet Query Builder, the list of values must be defined in the dialog box for entering values without the parenthesis, separating commas and inverted commas. Instead, values must be separated with a whitespace. The list of integers in the example above must be written into the field as:

  • 10 20 30
BETWEEN

Selection of a value range. The value is specified in the format:

LowerLimit ANDUpperLimit

Date

Integer

Real

  • ProjectGroup.SpendLimit BETWEEN 0 AND 10000

(finds projects with a spend limit higher than 0 and lower than 10000)

If the condition is written in the Alfabet query language as text and the values are dates, they must be written in inverted commas:

  • Appliation.StartDate BETWEEN ’25/03/2008’ AND ’28/03/2008’

(finds applications with a start date between March 25th, 2008 and March 28th, 2008)

In the Alfabet Query Builder, the upper and lower limit of the range must be defined in the dialog box for entering values without the AND, but only separated with a whitespace. Dates are not written in inverted commas in this dialog box.
NOT BETWEEN The object class property value is not to be found in a selection of a value range. The value is specified in the format:

LowerLimit ANDUpperLimit

Date

Integer

Real

  • ProjectGroup.SpendLimit NOT BETWEEN 0 AND 10000

(finds projects with a spend limit higher than 10000)

If the condition is written in the Alfabet query language as text and the values are dates, they must be written in inverted commas:

  • Application.StartDate NOT BETWEEN ’25/03/2008’ AND ’28/03/2008’

(finds applications with a start date before March 25th, 2008 and after March 28th, 2008)

In the Alfabet Query Builder, the upper and lower limit of the range must be defined in the dialog box for entering values without the AND, but only separated with a whitespace. Dates are not written in inverted commas in this dialog box.
= Equal to.

If the object class property value is of the type String , Date or Boolean , it must be written in inverted commas in the Alfabet query.

String

Date

Integer

Real

Boolean

Reference

ReferenceArray (with Reference Support set to True)
  • Application.StartDate = ’28/03/2008’

(finds applications with a start date of March 28th, 2008)

Wildcards are not allowed in value specifications for this operator.

In the Alfabet Query Builder, the value of any data type is written without inverted commas into the dialog box for entering values.
< Lesser than.

If the object class property value is of the type String or Date , it must be written in inverted commas in the Alfabet query.

String

Date

Integer

Real

  • Application.StartDate <= ’28/03/2008’

(finds applications with a start date before March 28th, 2008)

In the Alfabet Query Builder, the value of any data type is written without inverted commas into the dialog box for entering values.
<= Lesser than or equal to.

If the object class property value is of the type String or Date , it must be written in inverted commas in the Alfabet query.

String

Date

Integer

Real

  • Application.StartDate <= ’28/03/2008’

(finds applications with a start date of March 28th, 2008 and before)

In the Alfabet Query Builder, the value of any data type is written without inverted commas into the dialog box for entering values.
> Greater than.

If the object class property value is of the type String or Date , it must be written in inverted commas in the Alfabet query.

String

Date

Integer

Real

  • Application.StartDate > ’28/03/2008’

(finds applications with a start date after March 28th, 2008)

In the Alfabet Query Builder, the value of any data type is written without inverted commas into the dialog box for entering values.
>= Greater than or equal to.

If the object class property value is of the type String or Date , it must be written in inverted commas in the Alfabet query.

String

Date

Integer

Real

  • Application.StartDate >= ’28/03/2008’

(finds applications with a start date of March 28th, 2008 and higher)

: In the Alfabet Query Builder, the value of any data type is written without inverted commas into the dialog box for entering values.
<> Not equal to.

If the object class property value is of the type String or Date , it must be written in inverted commas in the Alfabet query.

String

Date

Integer

Real

  • Application.StartDate <> ’28/03/2008’

(finds applications with a start date other than March 28th, 2008)

In the Alfabet Query Builder, the value of any data type is written without inverted commas into the dialog box for entering values.
IS NULL

The object class property is undefined. It has either not yet been assigned a value or a previous value has been deleted by the user.

The operator has no value specification.

String

Date

Integer

Real

Reference

ReferenceArray

Boolean

  • Application.ApplicationGroups IS NULL

(finds all applications that are not assigned to an application group)
IS NOT NULL A value has been assigned to the object class property.

The operator has no value specification.

String

Date

Integer

Real

Reference

ReferenceArray

Boolean

  • Application.ApplicationGroups IS NOT NULL

(finds all applications that are assigned to at least one application group)