eSQL – Reference

=, <>, <, >, <=, >=

Relational operators

+, -, *, /

Arithmetical operators

*

Wildcard

$

Column query operator

ALL

Identifies all values with removing duplicates.

ALL VERSIONS

Inactive versions of object types are included in the query.

AND

Combines two conditions and returns TRUE if both conditions are met.

AS

Assigns an alias to an element. This operator is optional, i.e., the assignment also works without the operator being written into the clause.

ASC

Is used together with ORDER BY and specifies that the search results are sorted in ascending order.

AVG

Identifies the average value of a set of numbers and is therefore valid for numerical values.

containsrowwithcondition

Enables a condition to be formulated for a table type on an object type.

COUNT

Identifies the number of all entries of a column. Non-NULL-values are not counted.

currentdate

The currentdate function gives you the current date.

currentdatetime

The currentdatetime function gives you the current date and time.

currentlongdate

The currentlongdate function gives you the current date in long format.

currentlongdatetime

The currentlongdatetime function gives you the current date and time in long format.

currentuserid

The currentuserid function gives you the current user ID.

dateadd

Makes it possible to add time spans to date expressions or subtract them.

DESC

Is used together with ORDER BY and specifies that the search results are sorted in descending order.

DISTINCT

Removes all duplicates when identifying values.

EXCEPT

Combines the result sets of two queries under the condition that the results appear in type 1 but not in type 2.

EXISTS

Checks whether a sub-query returns at least one record.

false

Logical truth value

FROM

Specifies the data source of the search statement.

IN

Checks whether the specified value appears within a value list.

itemid

The itemid function gives you the item ID. For non-versioned types this is the sysId and for versioned types this is the item ID. All versions of an object instance have different sysIds, but always the same item ID.

JOIN

Links an object to a relation type.

length

Identifies the length of a string including spaces.

LIKE

Checks whether a string matches a particular pattern. On the right of the keyword LIKE there is a string that can contain the placeholder %.

LOWER

Converts all letters of a string into lower case.

LTRIM

Removes spaces at the beginning of a string.

MAX

Identifies the highest value in a set.

MIN

Identifies the smallest value in a set.

NO INHERITANCE

Specifies that deriving object types are not searched in the query.

NOT

Negates the Boolean expression specified in the statement.

NULL

Use IS NULL or IS NOT NULL to check whether a field is empty or not empty.

OR

Combines two conditions and returns TRUE if one of the conditions is met.

ORDER BY

Results are sorted according to one or several field names. The default is sorting in ascending order (ASC). DESC must be entered if results are to be sorted in descending order.

RTRIM

Removes spaces at the end of a string.

SELECT

Initiates a search statement.

substring

You can cut out a part of a string using the substring function.

The first int is the start position; the second int is the length.

SUM

Adds all elements of an object type and is therefore only valid for numerical values.

TRIM

Removes spaces at the beginning and end of a string

true

Logical truth value

typeid

The typeid function returns the ID of the specified type.

typeqname

Identifies the qualified name of the type for objects, relations, and all other types using the typeqname function.

UNION

Summarizes results of two queries without duplicates.

UPPER

Converts all letters of a string to upper case.

WHERE

Introduces a condition.