(DV-2205) WHERE condition

The WHERE condition in the table variant is the equivalent WHERE condition used in standard SQL syntax. While filter in table variant allows you to define a set of values for selection, the WHERE clause can be combined with AND, OR, and NOT operators and define a more complex selection set of data for comparison.

WHERE condition can be written in a popup window editor. The editor can be opened from the Table variant after clicking the set filter button and switching to the where condition tab. If the table variant has read-only mode then WHERE condition editor will be in read-only mode too. The editor can be opened only if the table name is set in the variant. Information fields like table name, Variant ID, RFC destination, Storage ID are displayed under the editing place.

In the WHERE clause only the following characters and operator can be used:

Single quotes	' '	For text values and special field names (contains special characters). They have to be always used in pairs.
The percent sign	%	Represents zero, one, or multiple characters. For usage as pattern character, the LIKE operator has to be used too. The '*' character can be used too if SM is not used.
The underscore	_	Represents a single character. For usage as pattern character, the LIKE operator has to be used too. The '+' character can be used if SM is not used.
The simple parentheses	( )	For grouping more expressions to simple logical result. They have to be always used in pairs.
Comparison operators	=; >; <; <=; >=; <>	Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE or FALSE
Logical operators	AND; OR; NOT	They return a true or false values to combine one or more true or false values
LIKE operator	LIKE	To match a pattern from wildcards characters ‘%' and '_’. The SQL LIKE operator is only applied on a field of types CHAR or VARCHAR to match a pattern

When SM storage ID is used a wildcard characters ‘*' and ‘+’ should not be used after the LIKE operator. After using the '%' character no other wildcard character can be used!

To validate the written WHERE condition click on

'Check where condition' button. Although it is not possible to save a Variant with an invalid WHERE condition, for editing purposes, with the confirm button it is possible to save an invalid WHERE condition for a Variant that is currently being edited. This lets the user switch between the Variant creation screen and between the WHERE condition editing screen.