Page Comparison

...

Table of Contents

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

...

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

Greater/Smaller Conditions

...

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.

...

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.

...

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.

...

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

...

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

IN

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

...

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.

...

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.

...

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.

...

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.

...