Query hints
Query hints (keyword HINT) can be used in the One Identity Manager query language to provide the query processor with additional data. Query hints are used internally to provide a context for additional permission queries to display columns that are only available when loaded as display values for foreign keys of another table.
HINT 'Name' = 'Value'
Example: Loading an identity that is referenced in a company structure as UID_PersonHead
FROM Person
WHERE PRIMARYKEY '<Key><T>Person</T><P>99918ef1-113f-480a-8e6e-704b1a3cf73a</P></Key>'
SELECT DISPLAYS
HINT 'SourceContext' = 'Org'
Conditions
Conditions combine all expressions that filter the resulting entities according to one or more criteria.
In the One Identity Manager query language, conditions can be linked with the AND and OR operators. You can overwrite operator precedence with curly brackets ({ }). Conditions can be inverted using the NOT keyword.
Example of a condition
WHERE Lastname = 'User'
AND (Firstname = 'Alex' OR Firstname = 'Sam')
Related topics
Special conditions
Selecting an entity using the primary key
n the One Identity Manager query language, use the PRIMARYKEY keyword to select the entity with the matching primary key. The primary key must be given in object key notation.
WHERE PRIMARYKEY 'Object Key'
Example
FROM Person
WHERE PRIMARYKEY '<Key><T>Person</T><P>99918ef1-113f-480a-8e6e-704b1a3cf73a</P></Key>'
SELECT DISPLAYS
Selecting an entity using a key
Use the KEY keyword to select the entity with the matching key. Keys can be object keys or alternative object keys, each in their XML notation.
WHERE KEY 'Key'
Example
FROM ADSAccount
WHERE KEY '<Key><Table Name="ADSAccount" Key="c149784b-6386-45d7-a38d-3c6e8e1b69d4"><Prop Name="UID_ADSAccount"><Value>c149784b-6386-45d7-a38d-3c6e8e1b69d4</Value></Prop></Table></Key>'
SELECT COLUMNS cn
Select using predefined SQL
With the LIMITEDSQL keyword you use a preformatted Where clause from the QBMLimitedSQL table. Any parameters used in it can be specified with the PARAMETER or the PARAMETERS clause.
WHERE LIMITEDSQL 'Identifier'
Comparing columns
Columns can be compared against another set of targets. In the One Identity Manager query language, all these comparisons start with the column name.
WHERE <Column> <Operator> <Operand>
The following operators are supported when comparing columns.
| = |
Equal |
Value that matches the column type, the parameter, or another column |
| <> |
Not equal |
Value that matches the column type, the parameter, or another column |
| < |
Less than |
Value that matches the column type, the parameter, or another column |
| > |
Greater than |
Value that matches the column type, the parameter, or another column |
| <= |
Less than or equal to |
Value that matches the column type, the parameter, or another column |
| >= |
Greater than or equal to |
Value that matches the column type, the parameter, or another column |
| LIKE |
Match with a specified pattern (as in SQL operator like) |
String or multivalued property |
| NOT LIKE |
No match with a given pattern |
String or multivalued property |
| STARTSWITH |
String starts with |
String or multivalued property |
| ENDSWITH |
String ends with |
String or multivalued property |
| CONTAINS |
String contains |
String or multivalued property |
| BITSSET |
The given bit positions are set |
Integer value or parameter |
| BITSNOTSET |
The given bit positions are not set |
Integer value or parameter |
Examples of column comparisons
WHERE Lastname = 'Einstein'
WHERE XDateInserted > 2020-02-01
WHERE Lastname STARTSWITH 'Ein'
WHERE XMarkedForDeletion BITSSET 2
