Functions

Functions are expressions that can be used as literals, references, and subselects in the SELECT or WHERE clause.

Current Functions

Current functions are functions which you can use to incorporate external parameters into the query. That could be the current time or the user executing the query, for example.

currentdate()

The currentdate function gives you the current date, or more precisely the time at the start of the current day, as an expression of the DateTime data type.

currentdatetime()

The currentdatetime function gives you the current time as an expression of the DateTime data type.

currentlongdate()

The currentlongdate function gives you the time at the start of the current day as an expression of the LongDateTime data type.

currentlongdatetime()

The currentlongdatetime function gives you the current time as an expression of the LongDateTime data type.

currentuserid()

The currentuserid function gives you the ID of the user executing the query. The example query returns all objects which the querying user has created.

SELECT [o]
FROM [object] AS [o]
JOIN [sysCreationTraceOwner2User]
JOIN [sysUser] AS [u]
WHERE [u].[sysId] = currentuserid()

currentsuborganisationid()

The currentsuborganisationid function gives you the ID of the querying user's suborganization.

This function is suitable for rights clauses, for example. If there is an object type whose objects are always only relevant for a very specific suborganization and should also only be visible for this suborganization, you can enter an ID element called suborganisationid for the object type when creating the ID of the suborganization for which the object is intended. Now if you create a visibility right with the eSQL clause '[suborganisationid]=currentsuborganisationid()', users to whom this visibility right applies can only see objects for which the ID of their suborganization is entered.

currentsuborganisationname()

The currentsuborganisationname function gives you the name of the querying user's suborganization. In the same way as for the currentsuborganisationid function, the named visibility scenario can also be represented with the currentsuborganisationname function.

DateAdd()

The dateadd function enables time intervals to be added to or subtracted from the DateTime expressions. The syntax is 'dateadd(<unit>, <value>, <outgoing date>)'

Here, the <unit> is an eSQL keyword from the set {year, month, week, day, hour, minute, second}, <value> is an integer expression, and <outgoing date> the date to which the value should be added.

If there is a 'paidon' element on the object type invoice, this statement can be used to find all invoices paid within the last week.

SELECT *
FROM [invoice]
WHERE [paidon] >= dateadd(week,-1, currentdate())

Here are a few more alternative formulations for the same query to demonstrate different uses of the dateadd function.

SELECT *
FROM [invoice]
WHERE [paidon] >= dateadd(day,-7, currentdate())

SELECT *
FROM [invoice]
WHERE dateadd(week,-1, [paidon]) >= currentdate()

SELECT *
FROM [invoice]
WHERE dateadd(minute,-60,dateadd(hour,-23,dateadd(day,-6,currentdate()))) <= [paidon]

ItemId()

The ItemID function can be used on all object types and returns the sysItemId for versioned object types and the sysId for unversioned object types. As you can also query elements directly, this function is not irreplaceable. But some requirements can be implemented more easily with this function.

Imagine a script that should identify the associated object for a given object type or object type name and a given ID. For unversioned object types, the caller would like to pass the sysId and for unversioned objects, the sysItemId. Instead of a case distinction which is not at all possible only with the object type name, you can use the following eSQL.

SELECT *
FROM <given object type name> AS [o]
WHERE itemid([o])=<given ID>

TypeId()

The typeid function returns the ID of the type of a value. For example, if you query an object type which has subtypes, and do not select the whole objects but only selected elements, the function can be useful in order to be able to identify the returned values in the result table.

SELECT sysid, sysitemid, typeid(o)
FROM [dms]:[sysObject] AS [o]

Or if you would like to count the objects grouped according to object type, you can do instead of using GROUP-BY  with the following query.

SELECT [tid], (SELECT count(*)
FROM [dms]:[sysObject] AS [o1]
WHERE typeid(o1)=[x].[tid])
FROM (SELECT DISTINCT typeid(o2) AS [tid]
FROM [dms]:[sysObject] AS [o2]
) AS [x]

The function applies not only to object types but also to all possible data types of the type system.

SELECT typeid([paidon]), typeid([amount])
FROM [dms]:[invoice]

SELECT typeid(1), typeid(1L), typeid(1.3), typeid('abc')

Typeqname

The typeqname function returns the qualified name of the type of a value. All examples for the typeid function can be reformulated in the same way as for the typeqname function.

SOUNDEX

The soundex function permits a phonetic search. It assigns a code to every string, whereby similar sounding strings are assigned to the same code. The following query finds all people whose last name is similar to 'meier.'

SELECT *
FROM [person]
WHERE SOUNDEX([last name]) = SOUNDEX('meier')

String Features

Substring

The substring function enables you to cut out a part of a string.

The syntax is 'substring(<string>, <start>, <length>).' substring('abcde', 2, 3) delivers the string 'bcd'.

Length

The length function is used in order to identify the length of strings including spaces. In the result of the following query, only customers whose first name has less than 10 letters are returned.

SELECT *
FROM [customer]
WHERE length([first name]) < 10

Ltrim

The ltrim function removes spaces at the beginning of a string.

ltrim('  abcde ') results in 'abcde '.

Rtrim

The rtrim function removes spaces at the end of a string.

rtrim(' abcde ') results in ' abcde'.

Trim

The trim function removes spaces at the beginning and end of a string.

trim(' abcde ') results in 'abcde'.

Upper

The upper function converts all lower-case letters to upper-case letters.

upper('aBcDe') results in 'ABCDE'.

Lower

The lower function converts all upper-case letters to lower-case letters.

lower('aBcDe') results in 'abcde'.

Aggregation Functions

Aggregation functions differ from other functions in two ways. They may only appear in the SELECT clause and not in the WHERE clause. They create a unique value based on all records that are in the FROM clause and fulfil the WHERE clause.

COUNT

Use the COUNT function to determine the number of records.

SELECT COUNT(*)
FROM [invoice]
WHERE [amount]>100

Instead of the '*,' a reference can also specify exactly what should be counted.

SELECT COUNT ([k].[name])
FROM [customer] AS [k]

Null values are not included in this.

Use ALL and DISTINCT to specify for the COUNT function whether everything should be counted or whether duplicates should be ignored.

SELECT COUNT (DISTINCT [k].[name])
FROM [customer]

SELECT COUNT (DISTINCT [k].[name])
FROM [customer]

If neither of the keywords is given, the query is executed with ALL by default.

MAX / MIN

The smallest and largest values of an element type are queried with the MIN and MAX functions.

SELECT MIN([amount])
FROM [invoice]

SELECT MAX([amount])
FROM [invoice]

MIN and MAX are permitted for expressions of the data types Integer, Long, Decimal, and LongDateTime.

SUM

The SUM function totals all values of an element type. The following query returns the total amount of all invoices whose amount minus discount is under €100.

SELECT SUM([amount])
FROM [invoice]
WHERE [amount] * (1 – [discount]) < 100

SUM is permitted for the data types Integer, Long, and Decimal. In the same way as the COUNT function, ALL or DISTINCT can be added.

AVG

The average value of expressions of data types Integer, Long, and Decimal can be shown with the AVG function. In the same way as for the COUNT function, ALL or DISTINCT can be added.

SELECT AVG([amount])
FROM [invoice]

If a SELECT clause contains an aggregate function, all references must be within aggregate functions.

The following is therefore not possible.

SELECT COUNT([name]), [k]
FROM [customer] AS [k]

In a SELECT clause, however, several aggregate functions may appear and also be combined with each other or with other expressions.

SELECT AVG([amount])+5, SUM([amount]*[amount]), MAX([amount])-MIN([amount])
FROM [invoice]

A query whose SELECT clause contains aggregate functions always leads to a result table with exactly one row.