Page Comparison

...

Table of Contents

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>)'

...

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.

...

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 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.

...

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.

...

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.

...

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.

...

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.

...