SELECT Clause

The SELECT clause consists of a list of expressions. Each of these expressions is of a particular data type. The list very often consists of just one expression and this expression contains references to objects and relations.

eSQL Return Tables

An eSQL instruction always returns a table. The SELECT clause determines how many columns the table has and what types the columns are. All types of the type system can appear – both simple types and class types. The result tables of most current examples consist of a column that has a class type as the column type. This example therefore delivers a single-column table with the complex column type person.

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

Select some of the elements of the dms:person object with the following eSQL statement. The statement delivers a table with five columns. You can also select further elements in this way.

SELECT [firstname], [lastname], [country], [city], [street]
FROM [person] AS [p]

The difference has the following practical effects:

• Objects which you can process further are selected in the first statement. If the statement is executed via a script or another interface, you can continue working with the objects.
• Elements are selected in the second statement. They determine a table with simple values, from which it is no longer recognizable whether the individual entries of a line belong together and come from one object. For this reason, you also cannot continue to work directly with source objects.

With most queries the user wants to identify whole objects or relations. Identifying individual elements is rarely useful. However, there are useful applications in which only individual elements are loaded.

For example, if you have already loaded a particular folder with its subfolders and now want to know which of these subfolders are empty, it would be unnecessary effort to reload whole objects again. With the following query you could load the IDs or ItemIDs or both of the non-empty subfolders and then compare the contained IDs with the available subfolders in the memory.

SELECT [sf].[sysId], [sf].[sysItemId]
FROM [sysFolder] AS [f]
JOIN [dms]:[sysParent2Child] AS [r1]
JOIN [dms]:[sysFolder] AS [sf]
JOIN [dms]:[sysParent2Child] AS [r2]
WHERE [f].[sysIdemId]=id'1234567890123456789012345678901A'

There are also hit lists with several columns which contain complex types.

The relation contract2contract creates a link between two contracts. The following eSQL statement delivers all pairs of contracts between which there is an instance of this relation.

SELECT [c1], [c2]
FROM [contract] AS [c1]
JOIN [contract2contract]
JOIN [contract] AS [c2]

The table has two columns.

If you need the relation between the two contracts, you can select this as well.

SELECT [c1], [r], [c2]
FROM [contract] AS [c1]
JOIN [contract2contract] AS [r]
JOIN [contract] AS [c2]

The order in the SELECT clause does not have to correspond to the order in the FROM clause. The example above can also be reformulated, for example.

SELECT [r], [c2], [c1]
FROM [contract] AS [c1]
JOIN [contract2contract] AS [r]
JOIN [contract] AS [c2]

Combined tables are also possible, i.e., tables that have columns with complex types and columns with simple types.

SELECT [c1], [r].[personincharge], [c2]
FROM [contract] AS [c1]
JOIN [contract2contract] AS [r]
JOIN [contract] AS [c2]

Asterisk

The asterisk symbol (*) can be used in the SELECT clause. This means that everything in the FROM clause is identified. It is just a resource for being able to formulate eSQL queries more easily.

Each eSQL query can also be formulated without the asterisk symbol. If the query has a large FROM clause and everything should be identified, the '*' symbol saves the need for individual references separated by commas on the FROM part. In addition, the asterisk symbol frequently saves the need for the assignment of aliases in the FROM part.

The table shows a few equivalent eSQL queries, with and without using the asterisk symbol.

All / Distinct

The additions DISTINCT or ALL can follow the keyword SELECT.

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

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

Duplicate lines are removed from the result table for DISTINCT. They are retained for ALL. If none of the words are specified, the query behaves as if ALL has been specified.

DISTINCT should only be used if it is really required, as it involves additional effort to search for and remove duplicate lines.

An example for which DISTINCT is appropriate is the selection of all non-empty folders.

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

A folder in which there are several objects would often appear in the result table accordingly.

Two rows are the same if the values in all columns are the same. Objects are the same if their IDs are the same. More precisely, the version ID must be the same, not the item ID.

The query

SELECT [p]
FROM [person] (ALL VERSIONS) AS [p]
WHERE [sysItemId]= id'1234567890123456789012345678901A'

therefore delivers all versions of a particular item. A DISTINCT would not have an effect here, as the versions have their own individual IDs.

Relations are the same if the first ID and the second ID are the same and they are of the same type. Here it must be noted that there can be multiple relations of different types between two objects.

Subselects

In the SELECT clause, subselects can also appear as an expression. However, a subselect must return a table with a column and one row at most for this. Often the property of having one row at most is ensured by an aggregate function.

With the following example, you receive all objects of the invoice type and also the number of locations for each object.

SELECT [r1], (SELECT count(*)
 FROM [dms]:[sysParent2Child]
 JOIN [dms]:[invoice] AS [r2]
        WHERE [r2].[sysId]=[r1].[sysid]
 )
FROM [invoice] AS [r1]

A subselect in the SELECT clause may contain references to outside, i.e., to the query surrounding the FROM clause. [r1].[sysid] is a reference of this type in the example. Such references are used very frequently.

In an aggregate function, the subselect always consists of exactly one row, such as count in the example. However, there are also queries in which the subselect does not contain an aggregate function. It is not so obvious then that it cannot consist of more than one row, and then it is also possible that it has no rows at all. Assuming that invoice is a folder type, in which it is ensured by relation constraints that an invoice can only have one child object per dms:sysParent2Child. With this example you receive all invoice objects and also their child object, if there is one.

SELECT [r1], (SELECT [o]
 FROM [dms]:[invoice] AS [r2]
 JOIN [dms]:[sysParent2Child]
 JOIN [dms]:[sysObject] AS [o]
        WHERE [r2].[sysId]=[r1].[sysid]
 )
FROM [invoice] AS [r1]

For an invoice which has no further child object, the subselect is empty. In the result table, the second column then contains zero.

There is only one subselect in each of the examples but there can be any number of subselects in a SELECT clause.

Literals

As well as references to class types, their elements or subselects can also contain literals in the SELECT clause.

For a particular file, the following query searches all folders in which the file is contained, and all DMS objects in the file.

SELECT [c], 1
FROM [dms]:[file] AS [p]
JOIN [dms]:[sysParent2Child]
JOIN [dms]:[sysObject] AS [c]
WHERE [p].[sysItemId]= id'1234567890123456789012345678901A'
UNION ALL
SELECT [p], 2
FROM [dms]:[sysFolder] AS [p]
JOIN [dms]:[sysParent2Child]
JOIN [dms]:[file] AS [c]
WHERE [c].[sysItemId]= id'1234567890123456789012345678901A'

The second column makes it possible to be able to assign which of the result objects belongs to which of the two sets when reading or viewing the result table.

As well as references, subselects, and literals, an element of the SELECT clause can be any expression.