Known issues and limitations

The operator ! is not accepted.

true and false are not accepted,

• use 1 instead of true
• use !=1 instead of false

null is not accepted, use instead the function ISNULL(attribute)

If you have an attribute which can be null, for example an enumeration with possible values: 'yes', 'no'

 SELECT MyClass WHERE enum != 'yes' 

is equivalent to

 SELECT MyClass WHERE enum = 'no' 

as SQL does not consider that null is different from 'yes'
In that case don't forget to explicitly request the null

 SELECT MyClass WHERE enum != 'yes' OR ISNULL(enum)

If the user has Allowed organizations defined, then this query will fail for him

SELECT l FROM Organization AS child 
JOIN Organization AS root ON child.parent_id BELOW root.id 
JOIN Location AS l ON l.org_id = child.id

Use instead

SELECT Location AS l 
JOIN Organization AS child ON l.org_id = child.id 
JOIN Organization AS root ON child.parent_id BELOW root.id

A query such as

SELECT P,L FROM Person AS P JOIN Location AS L

will return a different list depending on the user “Allowed organizations”

• without, he will get also Person without Location
• with, he will only get the Persons of his Allowed Organizations located on a Location belonging to one of his Allowed organizations, excluding the Persons that he can see, but which have no location

The syntax for LIKE is LIKE “%abc%”.

To escape a character, you have to prefix the character by \.
For example :

SELECT Contact WHERE name LIKE "%d\"a%"

The _ and % in the MySQL Like statement have special meanings, as documented in the MySQL documentation :

• _ to match any single character
• % to match an arbitrary number of characters (including zero characters)

If you need to search for those characters, in SQL you would escape them with a single \ character, but in OQL you have to prefix them with \\.
For example :

SELECT VirtualMachine WHERE name LIKE "%with\\_undescore%"
SELECT VirtualMachine WHERE name LIKE "%100\\% total%"