The One Identity Manager query language Select clauses describe the data that is fetched from the underlying database. You define the minimum amount of returned data. The object layer can select more data to meet arbitrary requirements. An example is the primary key, which is always selected, or special columns like the X columns.

Multiple select clauses can be combined into one query.

Different variations of the Select clause are supported.

Selecting specific columns of a table

SELECT COLUMNS <list of columns>

Example

FROM Person

SELECT COLUMNS Firstname, Lastname, CentralAccount

Select all columns of a table

SELECT ALL

Selecting all display values of a table

SELECT DISPLAYS

Display columns that are selected are:

• The table's display pattern (DialogTable.DisplayPattern)

• The table's display pattern (long) (DialogTable.DisplayPatternLong)

• Primary key of the table

In the case of returned entries, the table's display pattern and the display pattern (long) can be overwritten by a display value clause. For more information, see Display value clauses.

Selecting the table's display pattern

SELECT DISPLAYPATTERN

Selects only the columns from the table's display pattern or DISPLAY clause if it is used. This can provide improved performance over SELECT DISPLAYS because fewer columns are selected.

Select all columns of a table that are not marked as a BLOB field

SELECT NONLOBS

Selects all columns of the table that are not marked as a very long binary object or text object by the DialogColumn.IsBlobExternal column.

A matching entry exists

EXISTS

Determines whether an entry exists that fulfills the WHERE clause. The Exist clause overrides all other Select clauses except the Count clause.

Determine the number of matching entries

COUNT

Counts the entries that fulfill the where clauses. The Count clause overrides all other clauses.