Where clause

Allows to indicate a list of conditions that must be matched by the data in order to be processed within the level —For Each command or Data provider Group statement—. The conditions are boolean expresions, simples or very complex nested ones.

It is possible to specify more than one Where clause. In this case, the conditions are evaluated as if they were separated by an 'and' operator.

specifies any valid logical expression to condition the data retrieval. It can be a compound condition, using 'and', 'or' and 'not' logical operators.

Those attributes appearing in the boolean condition may be either from the for each Base Table or from its Extended Table. In some cases, attributes that do not belong to the extended table can be included. See Filters As Hint for more details.

The occurrence of n where clauses is equivalent to the occurrence of only one where clause, with the boolean conjunction of the conditions.

<condition> ::=

[not] <condition> [ {or | and} <condition>]

[(] <condition> [)]

<relationalExpr>

<stringExpr> like <stringExpr>

<booleanFunctionExpr>

<relationalExpr> ::=

<expr> [ {> | >= | = | < | <=} <expr> ]

<expr> is an expresion. If used for comparison, its datatype has to allow comparison (i.e. numeric, character, etc.). It can also be an aggregate formula (inline) (i. e: count, sum, min, max, average).

<bool_attribute>, <bool_variable> are attribute/variable of a Boolean datatype, respectively.

<bool_fuction> is a function returning a boolean result (True or False). <expr> is an expresion of the datatype required by <bool_function>.

<bool_proc> is a user defined proc that returns a boolean value.

<constraint>

is a <condition> that specifies when the where clause will apply. In client/server platforms, the When clause of each where clause is first evaluated, and if its condition is met, the filter specified by the where clause will be applied. See more about When clause.

<Condition> in the where clause constrain the dataset that is obtained. However, more than that is meant here. There is a great difference between a full scan table access and an index-optimized random access. If applying some rules of logic, GeneXus determines that the order clause (or the primary key order) is compatible with the where condition(s), it optimizes the access to the table using indices and not doing a full table scan. This is obviously the desired behavior whenever possible.

The conditions that can be optimized are the ones that use the operators: '>', '>=', '=', '<' and '<='.

If the Where condition is optimized, it will appear as "Navigation Filter" in the Navigation report. Otherwise, it will appear as "Constraint".

Be sure that the conditions used in the where clause respect type definitions. If this is not the case, an error message indicating type mismatch will be displayed.

Note the code is valid only as of GeneXus X Evolution 3 because we have included the Base Transaction clause in order to specify the base table. Remember this is not mandatory, and we could not include it. In such a case, the code will be valid for previous GeneXus versions.

CountryId, CustomerBirthDate and CustomerName belongs to CUSTOMER exteded table. Note we have no specified an order. This will affect the performance as you can see in Conditional Orders and Filters.

2. Now suppose &CountryId is set by the end user, by means of a web panel, for instance. As user can let the variable empty, we need the filter will not be applied in that case: