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
Comparison by means of IN and NOT IN clauses
In the One Identity Manager query language, comparisons with the keywords IN and NOT IN enable the comparison of a column with a set of values.
The values can be given
-
In list form
-
As a multivalued string, delimited by an ASCII 7 character
-
As a parameter containing a multivalued string delimited by an ASCII-7 character
Value lists
WHERE <Column> IN ( <Value>, <Value>, ...)
WHERE <Column> NOT IN ( <Value>, <Value>, ...)
All values must be of the same type and must be convertible to the column type.
Examples of value lists
WHERE StringColumn IN ('Value 1', 'Value 2', 'Value 3')
WHERE StringColumn NOT IN ('Value 1', 'Value 2', 'Value 3')
Multivalued string
WHERE <Column> IN 'Separated string value'
WHERE <Column> NOT IN 'Separated string value'
Example for multivalued strings
WHERE Lastname IN 'User1 User2 User3'
Parameters
Multivalued strings can be passed to queries by parameters.
WHERE <Column> IN @Parameter
Parameter examples
WHERE Lastname IN @Lastnames
You can also use MVP syntax. The normal IN clause syntax should be preferred in this case. For more information, see String values.
Compare date differences
A special case of column comparisons in the One Identity Manager query language are date difference comparisons. These compare the value of a column with a time range based on the current time.
WHERE <Column> <Operator> DATE <Integer> <Unit> AGO
| Years |
YEARS YEAR Y |
| Months |
MONTHS MONTH M |
| Weeks |
WEEKS WEEK |
| Days |
DAYS DAY D |
| Hours |
HOURS HOUR H |
| Minutes |
MINUTES MINUTE MIN |
| Seconds |
SECONDS SECOND S |
| Milliseconds |
MILLISECONDS MILLISECOND MS |
Examples
WHERE XDateInserted < DATE 3 MONTHS AGO
WHERE XDateInserted > DATE 5 MIN AGO
Compare date ranges
Comparisons of date ranges in the One Identity Manager query language check whether a date falls within a given date range.
Syntax for named ranges
WHERE <Column> IN RANGE <Range name> [ TIMEZONE 'Timezone ID'] [CULTURE 'Language code ID']
Syntax for a specific time range
WHERE <Column> IN RANGE <Start time> TO <End time> [TIMEZONE <Timezone ID>]
Area start time is included, end time is excluded.
Permitted identifiers are:
TODAY
YESTERDAY
THIS WEEK
THIS MONTH
THIS YEAR
LAST WEEK
LAST MONTH
LAST YEAR
LAST <integer> DAYS
LAST <integer> DAY
The names of the time zones must match the identifiers on the system running the query. If no time zone is given, UTC is used.
The language code indicates what day starts the week. It is only useful if one of the week ranges is specified.
Examples
WHERE XDateInserted IN RANGE YESTERDAY
WHERE XDateInserted IN RANGE YESTERDAY
TIMEZONE 'W. Europe Standard Time'
WHERE XDateInserted IN RANGE LAST YEAR
WHERE XDateInserted IN RANGE THIS WEEK
TIMEZONE 'W. Europe Standard Time'
CULTURE 'de-DE'
WHERE XDateInserted IN RANGE 2020-01-01 TO 2020-02-01
TIMEZONE 'W. Europe Standard Time'
Related topics
