You are browsing the documentation for iTop 2.7 which is not the current version.

Consider browsing to iTop 3.1 documentation

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. The following placeholders can be to define an OQL 'search menu' or a 'dashlet' which will return user dependent data.

Placeholder Comment
:current_contact->id
:current_contact_id
This placeholder returns the id of the person associated to the connected user. It returns 0 if no contact is associated to the current user
: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('2009-10-04 22:23:00')
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 elapsed since the first day of year 0
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


2_7_0/oql/oql_syntax.txt · Last modified: 2023/11/07 17:55 (external edit)
Back to top
Contact us