OQL Statements Syntax

There is currently only one type of statement in OQL: SELECT

The syntax of the SELECT statement is the following:

SELECT
    [output_specification FROM]
    class_reference
    [class_joined]
    [WHERE expression]
    [UNION oql_query]

output_specification indicates the class of objects that you want to retrieve, when omitted, it will default to class_reference. When it is made of a list of class aliases, then the first one determines what is the primary class expected.
class_reference indicates the class of objects that you want to query.
class_joined indicates a series of classes that you want to join, in order to restrict the set of selected objects (remember, it makes no sense to mention columns).
where_condition is an expression, very close to what could be found in an SQL SELECT statement.
Only since iTop 2.2.0 oql_query is another complete OQL query which results will be added to the results of the SELECT

output_specification

class_alias [, output_specification]

class_alias indicates an alias that is declared in the class_reference or class_joined clauses.

class_reference

class_name [AS class_alias]

class_name indicates the class of objects that you want to select.
class_alias indicates an alias that will be used to refer to the given class, in the expressions found into the WHERE clause.

class_name or class_alias

name | `name`

Backticks must be used in the following circumstances:

the name of the class is in conflict with a reserved word (example: JOIN…),
the name of the class contains undesirable characters.

class_joined

JOIN class_reference
  ON class_left.external_key join_operator class_right.id

class_reference refers either to the class on the left of the join or the right… depending on the data model and the given external key.
class_right.id has to be specified though it cannot be something else: it refers to the object that is pointed to by the other one. Class_right is an alias if any has been given.
class_left.external_key indicates which attribute from which class should be pointing to class_right.id. In most cases, the external key attributes could be guessed, but the reference has to be specified explicitely anyway. New in 2.2.0: the specified attribute can be of the type 'AttributeObjectKey'.
join_operator can be either =, BELOW, BELOW STRICT, ABOVE or ABOVE STRICT. BELOW and ABOVE can be used only in conjunction with attributes of type AttributeHierarchicalKey.

expression

literal
 | function
 | attribute
 | expression operator expression
 | (expression)

literal is either a string (enclosed with single or double quotes) or a number (only integers are supported). New in 2.0.1: hexadecimal notation is supported for unsigned integers up to 2^64 (example: 0x2F6C585B5FEACF7A without quotes).
function is one of the verbs listed above, the arguments are a coma separated list of expressions
attribute is a reference to an object property as defined in the data model, in the form class_ref.attribute_code – use of backticks is necessary to solve conflict with reseverd words or white characters.
operator is any of the binary operators listed below.
expression may be enclosed in parenthesis to cope with operators precedence.

Binary operators

Binary operators accepts two operands: one at the left and one at the right.

Operator	Description
AND	Logical AND
OR	Logical OR
/	Division operator
=	Equality operator
>=	Greater than or equal operator
>	Greater than operator
<=	Less than or equal operator
<	Less than operator
-	Substraction operator
!=, <>	Non-equality operator
LIKE	Simple pattern matching
NOT LIKE	Negation of simple pattern matching
IN	List operator
NOT IN	Negation of list operator
&	New in 2.0.1 Bitwise operator “and”. This operator is different from the “logical” operator “AND” since it operates on every bit of each number.
\|	New in 2.0.1 Bitwise operator “or”. This operator is different from the “logical” operator “OR” since it operates on every bit of each number.
^	New in 2.0.1 Bitwise operator “xor”.
<<	New in 2.0.1 Bitwise left shift
>>	New in 2.0.1 Bitwise right shift
REGEXP	Regular expression
MATCHES	New in 2.6.0 Fulltext match against a string. This operator only works with attributes of type TagSet. The supported syntax is `attribute MATCHES 'code1 code2`'

Wildcard

There are possibilities while writing your OQL to specify placeholders based on the current user and its contact associated.

Placeholder	Comment
:current_contact→id	to define an OQL 'search menu' or a 'dashlet' which will return user dependent data
`:current_contact→attribute`	where 'attribute' is any code attribute of the Contact class
`:current_user→attribute`	where 'attribute' is any code attribute of the User class

Example from the standard “Helpdesk” menu “Requests assigned to me (as Agent)”

SELECT UserRequest WHERE agent_id = :current_contact->id AND `status` NOT IN ('closed', 'resolved')

function

verb(expression[,expression [,expression...]...] ...])

verb is one the functions listed below

All functions are actually mapped to their equivalent in SQL. In other words, the same functions will be used in the resulting SQL queries that will be finally executed.

Therefore, the specification of those functions (number and type of arguments, returned values) is similar to the specification of the underlying database server. Any limitation or side-effect, will be related to the version of the database engine.

The hyperlinks provided hereafter will direct you to the reference documentation of mySQL 5.0, which is the standard recommended database engine (used for qualification of the OQL processor).

Function names are case-sensitive. They have to be uppercase in the current OQL implementation, though MySQL is less restrictive.
So far, no synomym has been implemented (There is just one single name for a given function ; example: OQL implements DAY whereas mySQL implements DAY and DAYOFMONTH as an alias to the same function)

Function name	Description	Examples
IF	If/else construct	IF(a=b, 'equals', 'differs')
ELT	Return string at index number	ELT(index, 'string1', 'string2', 'string3')
COALESCE	Return the first non-NULL argument	COALESCE(field1, field2, 'Undefined')
ISNULL		ISNULL(field1)
CONCAT	Return concatenated string	CONCAT(firstname, ' ', lastname)
SUBSTR	Return the substring as specified	SUBSTR('abcdef', 2, 3)
TRIM	Remove leading and trailing spaces	TRIM(' blah ')
DATE	Extract the date part of a date or datetime expression	DATE()
DATE_FORMAT	Format date as specified	DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')
CURRENT_DATE	Return the current date	CURRENT_DATE()
NOW	Return the current date and time	NOW()
TIME	Extract the time portion of the expression passed	TIME()
TO_DAYS	Return the date argument converted to days	TO_DAYS('2009-05-01')
FROM_DAYS	Convert a day number to a date	FROM_DAYS(12345)
YEAR	Return the year from the date passed	YEAR(DATE())
MONTH	Return the month from the date passed	MONTH(DATE())
DAY	Return the day of the month (0-31)	DAY(DATE())
DATE_ADD	Add time values (intervals) to a date value. See allowed interval units below	DATE_ADD(NOW(), INTERVAL 1 HOUR)
DATE_SUB	Substract time values (intervals) from a date value. See allowed interval units below	DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ROUND	Round the argument	ROUND(12.356, 2)
FLOOR	Return the largest integer value not greater than the argument	FLOOR(12.356)
INET_ATON	Return the numeric value of an IP address	INET_ATON('15.15.121.12')
INET_NTOA	Return the IP address from a numeric value	INET_NTOA(1231654)

The list of time interval units currently supported by the functions DATE_ADD and DATE_SUB, is a subset of the values allowed in MySQL.

OQL does accept:

Time interval units
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND

Sidebar