WHERE Clause

The WHERE clause is used to limit the result table. The WHERE clause contains a condition that must be met so that a row is included in the result table. A condition always has a truth value, TRUE or FALSE. A condition can be a simple condition, or several conditions linked together. This chapter presents all conditions and how they can be linked together.

There is a condition in the WHERE clause. There are operators available to help formulate this condition. These are presented in this chapter. Operators create a truth value from one or more expressions. For this, operators and expressions must be compatible, i.e., the expressions must be of the correct data types. The expressions can be simple elements of class types, literals, or more complex expressions made up from these.

Comparison Conditions

Comparison operators link two expressions to a truth value.

Equal and Unequal

For 'equal' and 'unequal,' expressions of the data types String, LongString, Boolean, Integer, Long, Decimal, DateTime, LongDateTime, Identifier, and Catalog entry can be compared with expressions of the same type.

In many cases, one expression is a reference to an element type and the other a literal.

SELECT *
FROM [customer]
WHERE [lastname] = 'schmidt'

SELECT [p]
FROM [dms]:[person] AS [p]
WHERE [sysId] = id'ABCDEF12345678901234567890123456'

Or both expressions are references.

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

However, the expressions can be any expressions that are simply one of the named data types and must be compatible. They may also be results of functions or operations or even subselects.

Note that for '<>,' a comparison with an element that is null is always untrue, i.e., the result of the query

SELECT *
FROM [customer]
WHERE [lastname] <> 'schmidt'

does not contain the objects for which the lastname element is null.

For an equal or unequal condition between an expression referring to a catalog entry and a string literal, the string literal is interpreted as a data representation of the catalog entry. This is assuming that the object type person has an element country that is of the catalog entry data type.

SELECT [p]
FROM [dms]:[person] AS [p]
WHERE [country] = 'Germany'

SELECT [p]
FROM [dms]:[person] AS [p]
WHERE [country] <> 'Germany'

Here, the string must be a literal. No reference to a string or function with the string type may be used.

Greater/Smaller Conditions

In these comparison conditions, only expressions of the data types Integer, Long, Decimal, DateTime, and LongDateTime are linked with expressions of the same type.

SELECT [w]
FROM [dms]:[warranty] AS [w]
WHERE [validFrom] >= datetime '2012-08-17'

SELECT [w]
FROM [dms]:[contract] AS [w]
WHERE [contractValue] < 1e4

The following query returns all articles whose price per item is greater than 10, for example.

SELECT [articles]
FROM [prices]
WHERE [priceperitem] > 10

As logical and comparison operators can be combined, you can also use them to create more complex conditions.

Linking Conditions

Conditions can be linked to new conditions with the following logical operators.

Here, the AND has a higher priority than OR, so you may need to use brackets to get the desired result.

The following query returns all unpaid invoices with an amount less than 100 or greater than 1,000,000.

SELECT *
FROM [invoice]
WHERE ([amount] < 100 OR [amount] > 1000000) AND [paid] = FALSE

Without the brackets, paid invoices with an amount less than 100 would also be included in the result.

LIKE

The LIKE operator checks whether a strings matches a pattern. A pattern can be any string with or without placeholders. The placeholder '%' can appear several times and stands for any number of characters.

All invoices that contain a comment with the letter 'a' are listed in this example query.

SELECT *
FROM [invoice]
WHERE [comment] LIKE '%a%'

All customers are listed whose last name begins with 's,' ends with 't,' and contains an 'm.'

SELECT *
FROM [customer]
WHERE [name] LIKE 's%m%t'

isParentOf

The isParentOf condition checks whether there is a path of directed dms:sysParent2Child relations between two objects. It enables searching to any depth and can be understood as a generalization of a JOIN link of two DMS object types using dms:sysParent2Child.

While

SELECT [c]
FROM [sysFolder] AS [f] JOIN [dms]:[sysParent2Child] JOIN [dms]:[person] AS [c]
WHERE [f].[sysid]=id'1234567890123456789012345678901A'

lists all persons who are directly in a particular folder, with

SELECT [c]
FROM [sysFolder] AS [f], [dms]:[person] AS [c]
WHERE [f].[sysid]=id'1234567890123456789012345678901A'
 AND [f] isParentOf [c]

you also receive persons who are in subfolders of this folder or in subfolders of subfolders, etc.

As all DMS objects are indirectly under DmsRoot, the query

SELECT [c]
FROM [sysRoot] AS [r], [dms]:[person] AS [c]
WHERE [r] isParentOf [c]

lists all persons and is equivalent to the query

SELECT [c]
FROM [dms]:[person] AS [c]

Is Null And Is Not Null

With these operators it is possible to check whether elements are empty (IS NULL) or not empty (IS NOT NULL).

In the following example all articles for which no value is entered in the purchase_price element type are queried.

SELECT [articles]
FROM [prices]
WHERE [purchase_price] IS NULL

IN

The IN condition checks whether a value is in a set of values.

The query lists all orders for which the element type price is one of the specified values (100, 200, or 300).

SELECT *
FROM [order]
WHERE [price] IN (100,200,300)

The values in the set after the keyword IN and the expression before the IN must all be of the same type here. All simple types apart from binary and Boolean are possible. The set can be specified in the list as in the example. However, a subselect is also possible.

isInOrganisationObjectIds

The isInOrganisationObjectIds condition is used to check whether the object belonging to the ID is an organization object that is superordinate to the querying user or the user themselves.

SELECT [o]
FROM [org]:[sysObject] AS [o]
WHERE isInOrganisationObjectIds([o].[sysId])

The condition is suitable for formulating rights clauses. For example, you can create an ID element type called orgObjectId and a visibility right with the eSQL clause isInOrganisationObjectIds([orgObjectId]) for an object type. When creating objects, you can now fill in the element orgObjectId with the ID of the organization object whose users should see the object.

isInParentOrganisationObjectIds

The isInParentOrganisationObjectIds function is used to check whether the object belonging to the ID is an organization object that is superordinate to the current user.

SELECT [o]
FROM [org]:[sysObject] AS [o]
WHERE isInParentOrganisationObjectIds([o].[sysId])

Querying Data from Catalog Systems

Catalog systems provide yuuvis® RAD users with an extensive and flexible option to create predefined selection options in a structured form for object fields of the catalog system type, in order to represent lists, trees, hierarchies, and also other multi-layered structures in a localized way.

A catalog system always has a defined amount of elements, called catalog system elements. These elements can be subordinate to other elements (subelements) or entries.

The entries are the actual values which can be selected by the user.

Subelements can display additional information for the entries.

Catalog systems can of course be included in eSQL queries. In order to query the value of the selected entry of a catalog system field in a WHERE clause, the following syntax must be used:

<Reference to element types referring to catalog entry> inCodeSystemData(<List of strings>)

Here, the string is interpreted as a data representation of the catalog entries.

An example: A simple catalog system has been created as a list, allowing to select the employee in charge of a project.

Now using a simple eSQL query all projects are returned, for which the catalog system field projectowner has the entry 'john doe.'

SELECT *
FROM [dms]:[projects]
WHERE projectowner inCodeSystemData('john doe')

All projects are output in which John and Jane Doe are not assigned to the catalog system field:

SELECT *
FROM [dms]:[projects]
WHERE projectowner NOT inCodeSystemData('john doe', 'jane doe')

In the case of a single-element list, the condition is synonymous with an '=' syntax.

"projectowner inCodeSystemData('john doe')" corresponds to "projectowner='john doe'"

A direct query using IDs is also possible:

WHERE codesystemfield NOT inCodeSystemIds(id'012345678901234567890123456789AA')

Subselects

There are several options for using subselects in the WHERE clause.

Exists

The exists condition checks whether a subselect is empty.

The example searches for all persons who have created at least one content file.

SELECT *
FROM [dms]:[person] AS [p]
WHERE exists (SELECT 1
FROM [ecm]:[sysCreationTraceOwner2User] AS [r]
WHERE [p].[sysid]=[r].[sysid1])

From the subselect, reference can be made outward to the FROM clause of the surrounding query. In the example, '[p].[sysid]' is a reference of this type. There are no further restrictions on the subselect. There can also be multiple expressions in the SELECT clause. However, as it is only of interest whether there is a result at all, a minimal SELECT clause such as 'SELECT 1' is sufficient.

In

For the IN condition, a subselect can also be used instead of an explicitly specified list. References to the FROM clause of the surrounding query are also permitted here. There may only be one expression in the SELECT clause. The single-column return table of the subselect is then interpreted as a list of values.

SELECT *
FROM [dms]:[person] AS [p]
WHERE [p].[sysid] IN (SELECT 1
FROM [ecm]:[sysCreationTraceOwner2User] AS [r])

Subselect as an Expression

If the subselect returns a table with a column and one row at most, it can be used as an expression. The example returns all invoices whose amount is greater than 90% of the maximum invoice amount.

SELECT *
FROM [invoice]
WHERE 10*[amount] > 9*(SELECT max([amount])
FROM [invoice])