References

In most eSQL queries, reference is made to the FROM clause from the WHERE or SELECT clause. The class types in the FROM clause or their elements or subselects in the FROM clause are addressed via their name or assigned aliases.

Type Names

Each type, whether complex or simple, user-defined or system, has a name and a qualified name.

Examples for object and relation types:

The name of system types always begins with 'sys' and the name of user-defined types never begins with 'sys.'

The name and qualified name only differ in the subsystem prefix for class types.

For most types, both names can be used in the FROM clause to access it. However, the qualified name is globally unique and the technical name is only unique within the subsystem. For the types dms:sysobject, org:sysobject, dms:sysparent2child, and org:sysparent2child, you must use the qualified name in order to make it clear which type is meant.

Through the API, you receive the name with the Type.getName() and Type.getNormalizedName() methods. Both methods differ only in case sensitivity, which is irrelevant for eSQL. You receive the qualified name through Type.getQualifiedName().

Element types also have a name and a qualified name. However, the name is fully sufficient for eSQL.

It does not matter whether you use the name or the qualified name in references to object types. A few example eSQL requests for references to the whole object type or to elements.

SELECT [dms]:[customer] FROM [dms]:[customer]

SELECT [customer] FROM [dms]:[customer]

SELECT [customer] FROM [dms]:[customer]
SELECT [dms]:[customer] FROM [customer]

Elements are accessed with <object type name or qualified object type name>'.'<element name>.

SELECT [customer].[sysid] FROM [customer]

The class type to which the element that is to be referenced belongs is unique; the element name is sufficient.

SELECT [sysid] FROM [customer]

Even if there are several types in the FROM clause, it can be unique. A relation does not have an element called sysid, so this query is permitted.

SELECT [sysid] FROM [customer] JOIN [dms]:[sysParent2Child]

Here it would not make sense to only use the element name, as it would not be clear whether the element sysid of the customer type or invoice type is meant.

SELECT [kunde].[sysid] FROM [customer] JOIN [dms]:[sysParent2Child] JOIN [invoice]

Alias

Aliases can be used after class types or subselects in the FROM clause or after expressions in the SELECT clause. The keyword AS can be used between the class type and the subselect or the expression and the alias, but it does not have to. In the same way as type names, aliases may consist of the characters {'a',…, 'z,' 'A',…., 'Z', '0',…, '9'} and have to start with a letter.

Aliases in the FROM Clause

The alias is used to give a new name to the class type, with which it can be referenced.

SELECT [k]
FROM [dms]:[customer] AS [k]
WHERE [k].[name]='doe'

This often has a simple practical reason. It is quicker to select this than the actual type name, which makes it easier to reference.

If the same type appears more frequently in the FROM clause, it is necessary to assign aliases in order to enable differentiation.

SELECT [parentfolder], [subfolder]
FROM [dms]:[sysFolder] AS [parentfolder]
JOIN [dms]:[sysParent2Child]
JOIN [dms]:[sysFolder] AS [subfolder]

The example shows another use case. It assigns meaningful names to the types according to their role in the statement. On one hand, this increases the legibility of the eSQL statement itself, and on the other these names appear as titles in the result table.

The alias 'overwrites' the class type name to a certain extent. When using aliases, the class type can only be addressed with the alias and no longer with its actual name.

Even a subselect in the FROM clause must have an alias. The following example finds all folders in which there is an e-mail or invoice.

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

The example shows another use for aliases. For UNION links, you can use aliases to assign joint names for both combined expressions in order to access them from outside.

If you omit the aliases in the subselect

…
( SELECT * FROM [dms]:[email]
UNION ALL
SELECT * FROM [dms]:[invoice]
) AS [u]
…

the reference in the WHERE clause [u].[o].[sysid] would no longer be possible.

When entering this reference into the subselect, the front part can also be omitted as long as it remains unique. For example, the WHERE clause can be shortened to

WHERE [r].[sysid2]=[o].[sysid]

The '[o].' cannot be omitted, as sysFolder also has a sysid. Therefore, the reference would no longer be unique.

However, if you consider a similar query that finds all e-mails and invoices, which have at least one child object,

SELECT [u].[o]
FROM( SELECT [o] FROM [dms]:[email] AS [o]
UNION ALL
SELECT [o] FROM [dms]:[invoice] AS [o]
) AS [u],
[dms]:[sysParent2Child] AS [r]
WHERE [r].[sysid1]=[u].[o].[sysid]

you can also omit the '[o].'. All other references can also be shortened.

SELECT [o]
FROM( SELECT [o] FROM [dms]:[email] AS [o]
UNION ALL
SELECT [o] FROM [dms]:[invoice] AS [o]
) AS [u],
[dms]:[sysParent2Child] AS [r]
WHERE [sysid1]=[sysid]

Aliases in the SELECT Clause

In the SELECT clause, aliases are mainly used  to have meaningful titles in the result table.

SELECT count(*) AS [countInvoices] FROM [dms]:[invoice]

SELECT [f] AS [Folder], [c] AS [Document]
FROM [dms]:[sysFolder] AS [f]
JOIN [dms]:[sysParent2Child]
JOIN [dms]:[sysDocument] AS [c]

However, aliases can also be used in order to create a joint name for UNION links in subselects.

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

Masking

Type names and aliases can optionally be masked with square brackets. This is only mandatory if a name or alias is the same as an eSQL keyword.

SELECT subject, to, [from]
FROM email

For qualified names, each component of the name must be in separate brackets.

SELECT * FROM [dms]:[person]

For each part of a name or alias, it is possible to decide independently whether to use brackets. A name or alias which appears often in eSQL does not need to be masked or not masked in all locations.

SELECT [dms]:person.[firstname] FROM dms:[person]

SELECT p.firstname FROM dms:person AS [p]

SELECT [p].firstname FROM dms:person AS p

Case Sensitivity

Case sensitivity does not play a role in type names and aliases.

The following examples are valid eSQL queries.

SELECT [person] FROM [Person]

SELECT [PARENT], [child]
FROM [dms]:[sysFolder] AS [parent]
JOIN [dms]:[sysParent2Child]
JOIN [dms]:[Sysobject] AS [Child]