FROM Clause

In the simplest case, the FROM clause consists of just one class type. However, it can consist of several class types separated by commas.

SELECT [f]
FROM [sysFolder] AS [f], [dms]:[sysParent2Child]

A request of this type is only then sensible if the individual types are linked with suitable conditions in the WHERE clause, e.g., by ID comparisons.

SELECT [f]
FROM [sysFolder] AS [f], [dms]:[sysParent2Child] AS [r]
WHERE [f].[sysId] = [r].[sysId1]

The example delivers all non-empty folders.

The FROM clause can also contain considerably more types.

SELECT [f]
FROM [sysFolder] AS [f], [dms]:[sysParent2Child] AS [r1], [dms]:[person] AS [p], [ecm]:[syscreationtraceowner2user] AS [r2], [org]:[sysuser] AS [u]
WHERE [f].[sysId] = [r1].[sysId1] and [r1].[sysId2] = [p].[sysid] and [p].[sysid]=[r2].[sysId1] and [r2].[sysId2]=[u].[sysId] and [u].[sysname] = 'root'

The example delivers all folders in which an object of the Person type is contained, which in turn was created by the user root.

The order in the FROM clause does not play a role. The way the types should be linked is clearly defined in the WHERE clause.

JOIN

In the second example you can already see that the link of several types in the FROM clause can quickly lead to a long and confusing WHERE clause. In addition, there is the keyword JOIN that is essentially only an abbreviating notation for the ID comparison between relation and object types. It is set between two class types of the FROM list instead of a comma. One of the class types must be a relation type and the other an object type. Using JOIN, the WHERE clause is added implicitly by a comparison of the ID of the object type with the first or second ID of the relation type, depending on the order of the object type and relation type. The example just seen can be reformulated in the following way.

SELECT [f]
FROM [sysFolder] AS [f] JOIN [dms]:[sysParent2Child] AS [r1] JOIN [dms]:[person] AS [p] JOIN ecm]:[syscreationtraceowner2user] AS [r2] JOIN [org]:[sysuser] AS [u] WHERE [u].[sysname] = 'root'

In addition, another check is carried out on the compatibility of types when using JOIN.

The following example therefore results in an error message, as dms:sysParent2Child on the first page is only compatible with the dms:sysFolder or subtypes of dms:sysFolder.

SELECT [u]
FROM [ecm]:[org] AS [u] JOIN [dms]:[sysParent2Child] AS [r]

If JOIN is replaced with the directly written ID comparison,

SELECT [u]
FROM [ecm]:[org] AS [u], [dms]:[sysParent2Child] AS [r]
WHERE [u].[sysid] = [r].[sysid1]

that is indeed a permissible eSQL that still delivers an empty results table.

Modifiers

If you access a class type with an eSQL query, you can add modifiers to it. These are written directly after the class types in brackets, and separated by a comma in case of several modifiers.

SELECT [p] FROM [dms]:[person] (NO INHERITANCE) AS [p]
SELECT [p] FROM [dms]:[person] (ALL VERSIONS) AS [p]
SELECT [p] FROM [dms]:[person] (ALL VERSIONS, NO INHERITANCE) AS [p]
SELECT [p] FROM [dms]:[person] (NO INHERITANCE, ALL VERSIONS) AS [p]
SELECT * FROM [dms]:[person] (ALL VERSIONS)
SELECT [r] FROM [dms]:[sysParent2Child] (NO INHERITANCE, ALL VERSIONS) AS [r]

The modifiers may only be used in statements in the FROM clause directly after the type name. They must not be used after an alias or a reference.

All Versions

When querying versioned object types, by default only the current versions are taken into account. The ALL VERSIONS modifier is used if you also want to include obsolete versions.

SELECT [p]
FROM [person] (ALL VERSIONS) AS [p]

When querying ALL VERSIONS, leaving out the modifier is synonymous with the condition sysVersionMostRecent=true. Both of the following queries are therefore equivalent.

SELECT [p]
FROM [person] AS [p]

SELECT [p]
FROM [person] (ALL VERSIONS) AS [p]
WHERE [sysVersionMostRecent]=true

The ALL VERSIONS modifier may also be used in unversioned object types such as for example org:sysUser, but does not have any effect here. For heterogeneous object types, i.e., types with both unversioned and versioned subtypes, such as e.g., tps:sysBusinessObject or tps:sysObject, the modifier is also allowed. For its versioned subtypes, it works as described and it is ignored for its unversioned subtypes.

Relations themselves are not versioned. However, a versioned object can be at both ends of a relation. All relations are taken into account with the ALL VERSIONS modifier. Without it, relations for which there is a versioned object that is an old version on at least one page are filtered out, and the connection versioning type is IgnoreVersioning here.

The relation dms:sysParent2Child has the connection versioning type IgnoreVersioning on both sides. Let's consider two DMS objects that are both connected by dms:sysParent2Child and are both third versions. Between every version of one object and every version of the other object, there is a dms:sysParent2Child relation, so 3*3=9 relations in total. These are closely linked but are still independent relations. The result table of the query

SELECT [r]
FROM [dms]:[sysParent2Child] (ALL VERSIONS) AS [r]

contains all 9 of these relations. Without the ALL VERSIONS modifier, it would only contain one of the relations, namely that between the respective current versions of the objects.

It is different for the relation ecm:sysCreationTraceOwner2User. On the first page, it has the connection versioning type Version. Let's consider another DMS object that consists of three versions. There is a relation between each version and the respective creator of the version. The result of the query

SELECT [r]
FROM [ecm]:[sysCreationTraceOwner2User] AS [r]

contains all three of these relations. Adding the ALL VERSIONS modifier has no effect here.

If there are several class types in the FROM clause, for each it is independently evaluated whether the ALL VERSIONS modifier is set or not. For one type the modifier may be ineffective if the type is joined to another type without the ALL VERSIONS modifier. Let's consider the example again with two DMS objects connected by dms:sysParent2Child and both in the third version. The following table shows how many of the 9 relations are contained in the result table for different queries.

And a few example queries on the connection versioning type Version. It is indicated how many of the three ecm:sysCreationTraceOwner2User relations between the three versions of an object and the respective creator are contained in the results table.

As described, the omission of ALL VERSIONS for object types ensures that the condition is implicitly supplemented by sysVersionMostRecent=true. A query with ALL VERSIONS is therefore somewhat simpler and performs better, if applicable. Typical use cases for ALL VERSIONS are if an ID is given and the user wants to identify the associated object or child object.

SELECT [p]
FROM [dms]:[sysObject] (ALL VERSIONS) AS [o]
WHERE [o].[sysId] = id'1234567890123456789012345678901A'

SELECT [c]
FROM [dms]:[sysFolder] (ALL VERSIONS) AS [p]
JOIN [dms]:[sysParent2Child] (ALL VERSIONS)
JOIN [dms]:[sysObject] AS [c]
WHERE [p].[sysId] = id'1234567890123456789012345678901A'

No Inheritance

When querying class types, by default all subtypes are also searched. NO INHERITANCE is used if you only want to search in the specified object type.

SELECT *
FROM [customer] (NO INHERITANCE)

The modifier can only be used for non-abstract types and only has an effect if this type does not have further subtypes. However, non-abstract types that have subtypes are uncommon.

Include Recycle Bins

Normally recycle bins and objects in the recycle bin are not visible. With the modifier INCLUDE RECYCLE BINS, however, an individual recycle bin or all recycle bins can be made visible, if the user has the right to see other users' recycle bins.

Subselects

In previous examples, the FROM clause consisted of a list of class types. As well as class types, subselects are also permitted as a component of a FROM clause. The subselect itself is a valid eSQL query. It must be in brackets and must have an alias.

The query

SELECT [p]
FROM [person] AS [p]

can thus be reformulated as

SELECT [x].[p]
FROM (SELECT [p] FROM [person] AS [p]) AS [x]

for example.

This query identifies all non-empty folders.

SELECT [f]
FROM [sysFolder] AS [f]
JOIN [dms]:[sysParent2Child] AS [r]
JOIN [dms]:[sysObject] [o]

However, now the criterion is not that just any DMS object should be contained in the folders, but rather the folder in which there is at least one object with the type dms:rechnung or dms:email. This can be done in the following way with a subselect.

SELECT [f]
FROM [sysFolder] AS [f]
JOIN [dms]:[sysParent2Child] AS [r],
(SELECT [o] FROM [dms]:[email] AS [o]
 UNION ALL
 SELECT [o] FROM [dms]:[rechnung] AS [o])
 AS [u]
WHERE [r].[sysid2]=[u].[o].[sysid]