Object Query Language (OQL) - Syntax Reference

Object Query Language (OQL) - Syntax Reference	Contents
	OQL Syntax Reference
	The OQL Filter/Expression Editor

ReportsAnywhere uses the Object Query Language.

OQL is a superset of SQL and a superset of the EJB3 query language.

An OQL expression returns values (like Strings or numbers), objects (as defined by the underlying datasource e.g. Java Objects, or XML elements) or collections (Lists of objects).

Compared to a full SQL query ReportsAnywhere's expressions are equivalent to the where part of the SQL query.

An important concept of a OQL expression is its scope. It defines which data members from your data source are accessible in your expression.

Note: The scope within a table is the element type of the collection, that is the source of the table. The fields outside the collection are not directly accessible. (unless you use the built-in functions from() or root()) This concept may be new to SQL experienced users, but it results from the object oriented principle of encapsulation and it has lots of benefits. (E.g. reuse)

How OQL expressions are composed

Literals

The atomic element are the literals which are either strings, numbers or field names. (E.g. 3.5, "Hello World" , zipCode, true)

Note: Numbers are always written with a dot as decimal independent of your language settings. They must have a leading digit. (0.5 not just .5)
Strings are written in double quotes. Field names are case sensitive.

Note: The literal null denotes a null value, but you can not use it in comparisons. zipCode = null is always false. This may be confusing for programmers, but is mandated by SQL. Simple literals like numbers also have a null value.

Arithmetic

Literals and subexpressions can be combined into the usual arithmetic expressions.

E.g. netVal * taxRate
zipCode + " " + cityName
rowNumber mod 2

Comparisons

Values and objects can be compared.

netVal < 1000
orderingParty = receivingParty

Note: Object comparison uses the object identity of the datasource (Primary key, OID or Java equality etc).

Note: Arithmetic comparisons are in double precision math. Beware of rounding errors.

Object Navigation

This is the main extension over SQL, which is also supported by EJB3. You can navigate from an object to linked objects using the dot notation. This is the familiar notation used by Java and other programming languages.

E.g. orderingParty.address.zipCode

Note: null objects on the navigation path do not throw exceptions. Simply the whole navigation expression return null. So in the above example it does not matter which of the fields orderingParty, address, zipCode return null if one of them is null the whole navigation expression will be null.

Logical expressions

Subexpressions can be combined using the operators and, or, not.

Note: You have to use the SQL word operators, not the programming language operators &&, ||, !

Functions

You can use a number of built in functions. These are documented see here

Examples:

isNull(orderingParty)	This is the proper way to check for nulls.
concat(" ", zipCode, city)	Smart concatenation. Only oputputs the separator blank if zipCode and city are non empty.
case(shippingState, "shipped", invoiced", "ordered")	maps integers to strings.

Note: Functions are not written as mehods. So you must write toUpper(cityName) instead of cityName.toUpper()

Conditional expressions

Qint OQL supports the conditional expression operator ?: as known from C/Java.

Example:

isNull(object)? "null": "not null"
b = 0 ? 0 : a/b

a more complex example:

(isNull(zipCode) or isNull(cityName))?(zipCode+cityName):(zipCode + " " + cityName )

Collection subexpressions

With collection subexpressions you can check whether one or all elements of a nested collection fulfill a criterium:

E.g.

for all x in items: x.product.inStock > 0	checks if all items are in stock.
exists x in items: x.product.price > 1000	checks if order contains an expensive item
count(filter x in items: x.product.supplier.name = "supplierx")	counts the number of items from supplierx in an order.

Index operator

You can retrieve an element from a collection or or a sub-collection.

items[3]	retrieves the 3rd item from a collection.
items[count(items)-1]	retrieves the last item. But last(items) is simpler.
items[10..20]	retrieves a collection with the elements from 10 to 20.
items[10..]	retrieves a collection with all elements after the 10th.

Note: Some data sources automatically order collections. (Java, XML, Cach�). Others like SQL must have an explicit sorting specified.

Cast expressions

In an object oriented setting like Java, Caché or XML a link may reference a base class, where a subclass is actually used. Therefore you can cast an expression like in Java/C

((Service)item.product).requiredSkill

This is used if your item may refer to either an ordinary product or to a service (which has additional fields)

An important use of the cast is the from() built-in function which returns the enclosing scope:

((Invoice)from()).orderingParty.rebate