(DV-2208) Dynamic condition
- SNP
- Marian Vidra
The dynamic condition in the table variant is the equivalent of the WHERE and either the JOIN 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. The JOIN condition allows to do selection based on data in another DB table.
Both dynamic conditions can be written in the 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. The editor can be opened only if the table name is set in the variant. Information fields like table name, Variant ID, RFC destination, and Storage ID are displayed under the editing place. If the table variant has read-only mode then the dynamic condition editor will be in read-only mode too.
JOIN condition is OPTIONAL. Only one table can be combined with the main table (defined in the variant) via the JOIN condition. No brackets are allowed in the join condition at the beginning. If the JOIN condition is defined then the main table has alias T1 and should be used for all mentioned fields in the join and where condition. Writing of join condition is not case sensitive. Writing the JOIN condition is not possible for SM tables.
Example of JOIN condition
For the WHERE condition type only the condition as for the SELECT statement.
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 a 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 a 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 results. 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 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, wildcard characters ‘*' and ‘+’ should not be used after the LIKE operator. After using the '%' character no other wildcard character can be used!
Example of WHERE condition on SM table
To validate the written JOIN or WHERE condition, click on the 'Check' button.
The check is performed on the corresponding system with respecting RFC destination.
Although it is not possible to save a Variant with an invalid JOIN or 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 the dynamic condition editing screen when the condition is not finished.