Data queries in the One Identity Manager query language always start with the FROM keyword and a table name. An optional alias for the table can be specified after the AS keyword.
FROM <table name>
FROM <table name> AS <table alias>
An alternative is to query directly using an object key.
Example of the query in long form
FROM Table WHERE PRIMARYKEY '<Key><T>Table</T><P>UID</P></Key>'
Example of the query in short form
FROM '<Key><T>Table</T><P>UID</P></Key>'
You can specify a list of clauses after the query header. The clause types do not have to be in any particular order.
Where-clauses in the One Identity Manager query language start with the WHERE keyword and contain a condition that the resulting data must match. Unlike SQL syntax, multiple Where clauses can be combined in a One Identity Manager query language query.
Examples of Where clauses
WHERE Lastname = 'User1'
WHERE Lastname = 'User1' AND Firstname = 'Chris'
WHERE [isnull(lastname, '') = N'User1]
WHERE Lastname IN ('User1', 'User2')
WHERE XDateInserted IN RANGE LAST MONTH
Related topics
In the One Identity Manager query language the SEARCH keyword is used to search for all matching entries from the full text index. To use this type of clause, a full text index must be installed and accessible from the application.
SEARCH 'Search string'
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:
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.
