Scope
Objects: Transactions, Work Panels, Web Panels, Procedures, Reports
Languages: C/SQL, Java, .NET, Visual Basic, Visual FoxPro
Interfaces: Web, Win
DBMS: DB2/UDB, Informix, Oracle, SQL Server
Introduction
Starting in GeneXus Olimar version, it will be possible to condition the search orders and filters used in GeneXus objects or Subfile-type controls, by using the new WHEN clause.
This will enhance the performance of the queries that include several search criteria, from the moment that the search order and the appropriate conditions may be coordinated.
Description
Filter Conditions
You may condition the filters specified at the For Each command level, at an object's general Conditions level or at a Subfile level. This way, a particular filter will be applied when a given condition is met. If the condition applied to the restriction is not met, the filter will not be taken into consideration.
The conditions' syntax (WHERE, etc.) in the Olimar version is the following: Condition [WHEN Constraint];being:
Condition is a GeneXus condition, and Constraint is the condition that enables Condition.
Conditional filter conditions are accruable (using AND) among themselves, and there are cases in which no restriction will be applied if all the corresponding conditions are false.
For a conditional restriction to be generated as such, it is necessary to be on a Client/Server framework and the Condition has to be "assessable" by the DBMS that is being used. This means that GeneXus has to know how to write the Condition in the same language of the DBMS that is being used.
If it cannot be generated as such (because the target generator cannot support it or because the condition cannot be written in the DBMS' language), it will become an "ordinary" filter by substituting a WHEN by an OR. Moreover, the spc0053 code message -'Unsupported conditional constraint "%1" changed to standard constraint %2.'- will be generated in the browsing diagram. (The specification in design mode takes up an SQL generation in an undetermined DBMS).
Browse Listing
When conditional filters are defined, the browsing diagram will show this as a conditional Constraint, and it does not participate in the Start From/LoopWhile determination.
Generated Sentences
Sentences that are generated for conditional filters are similar to the ones generated for standard filters (without the WHEN clause). The main difference is that all the possible strings are available, and the corresponding ones are concatenated depending on the conditions. Moreover, the values of variables are instanced.
Example
- The CliAdd filter = &CliDir or null(&CliAdd) generates the following sentence:
SELECT [CliAdd], [CliName], [CliId] FROM [CLIENTS] (NOLOCK) WHERE [CliAdd] = ? or (?=' ') ORDER BY [CliId]
- If this filter becomes conditional: CliAdd = &CliAdd WHEN not null(&cliAdd) the generated sentence will be as follows:
1. When the &CliAdd variable is null:
SELECT [CliName], [CliId] FROM [CLIENTS] (NOLOCK) ORDER BY [CliId]
2. When the &CliAdd variable has a value:
SELECT [CliAdd], [CliName], [CliId] FROM [CLIENTES] (NOLOCK) WHERE [CliAdd] = 'Miguelete 1234' ORDER BY [CliId]'
Conditional orders
You may specify alternative browsing orders depending on the application's needs. The corresponding syntax is as follows:
[ORDER] OrderAttList [WHEN Constraint1 ]
[ORDER OrderAttList1 [WHEN Constraint2 ]
...
OrderAttListX is a list of attributes which are separated by spaces or commas that indicate the browsing order. If a descending order is desired for any of the attributes, it should be placed between parentheses.
ConstraintX is the condition that enables the corresponding OrderAttList.
If none of the Constraints are true, and there is no unconditional Order (without the WHEN clause), the browsing order becomes undefined. Undefined, in this case, means that it may vary from DBMS to DBMS, and also, in successive executions.
If more than one of the Constraints is true, the order from the first ORDER clause defined is adopted; for this reason, it should be the preferred order for queries.
If conditional orders are specified, but the generator does not support this, the browsing order will be the one specified as an unconditional order, or, when there is not one, the primary key of the group's base table. Moreover, the spc0054 code message -' Unsupported conditional order %2. Using default order in %1.'- will be generated in the browsing diagram. (The specification in Design mode takes up an SQL generation in an undetermined DBMS).
Conditional orders are not supported when Breaks are performed. The spc0055 code message -' Conditional order in group starting at line %1 not allowed (Break group).'- will be generated in the browsing diagram.
General considerations
- They only apply to generators that use SQL as a method to access the database.
- If the Constraint has attributes, these are considered as instanced. In other words, the Constraint is evaluated before the browsing begins and does not change while it is being performed.
- Performance
Because of the way in which they are implemented, groups with conditional orders or filters are not expected to be used inside high-level-occurrence loops. They could cause performance problems, since the SQL sentences are put together dynamically, and the "analysis" time that the DBMS must take, in each execution, may be quite high for these situations.
Notwithstanding the above-mentioned considerations, major improvements are expected in the performance of the "tell-me-the-data-it-has-and-I-ll-look-for-it" type of queries, since by using the appropriate conditions, the filters and the search order may be coordinated.
Example 1
The following example corresponds to a filter condition:
ClientName LIKE &ClientName
WHEN NOT null(&ClientName);
ClientSSN = &ClientSSN
WHEN NOT null( &ClientSSN);
The way to interpret the above-mentioned code is as follows:
If the &ClientNamevariable has a value, then GeneXus will apply the ClientName LIKE &ClientName filter. If, moreover, the &ClientSSN variable has a value, it will also apply the ClientSSN = &ClientSSN filter in an AND relation with the other filters.
If none of the variables have a value, then no filter will be applied.
Example 2
For each ClientName WHEN NOT null(&ClientName)
Order ClientSSN WHEN NOT null( &ClientSSN)
...
Endfor
In this For Each, the browsing will be performed following a per ClientNameorder, if the &ClientName variable has been assigned a value. If there is no value assigned, then the order will be performed according to ClientSSN if &ClientSSN has been assigned a value. If none of the previous conditions are met, the browsing will be performed following an undefined order.
For each ClientName WHEN NOT null(&ClientName)
Order ClientSSN WHEN NOT null( &ClientSSN)
Order ClientSSN
...
Endfor
In this other For Each example, the browsing orders are similar to the first one, except for the case in which none of the Conditions are met, due to the existence of an unconditional Order. In this case, the browsing is performed following the unconditional Order.