When clause

To specify when the condition for data retrieval or the order of data displayed will be used.

Syntax

condition | order_attributes… [ when constraint ]

Where:
condition
    specifies any valid logical expression that conditions the data retrieval. 

order_attributes
    specifies a list of attributes delimited by spaces or commas that indicate the navigation order.

constraint
    enables the condition or the order.

Description

The When clause can be specified to restrict a condition for data retrieval or the order of data displayed. It may be used in the Where clause and in the Order clause of the For Each command, Xfor Each command and Xfor First command.  It also applies to Grid control conditions and Grid control orders.

Notes

• Conditional filter conditions are additions (with AND) among them, and it is possible that no constraint apply, when all corresponding conditions are false. 
• They only apply to generators using SQL as method to access the database. 
• To generate a conditional constraint as such, you must be in a Client/Server architecture and the DBMS used must be able to “evaluate” the Condition . For example: GeneXus must know how to write the Condition in the language of the DBMS used.
• If it cannot be generated as such (because the destination generator does not support it or because the condition cannot be written in the DBMS language) it will be transformed into a “standard” filter substituting the WHEN with an OR.
• If the Constraint has attributes, these are considered as instantiated. For instance: the Constraint is evaluated before starting the navigation and it does not change during navigation.
• If none of the Constraints are true, and there is no unconditional Order (without the When Clause), the navigation order is undefined. In this case, undefined means that no order is indicated. Therefore, the order to be applied may vary from one DBMS to another, even in successive executions. 
• If more than one Constraint is true, the order taken is the first ORDER clause defined. This is why this order should be the preferred one for queries. 
• Conditional orders are not supported with Breaks.

Examples

Example of a filter condition

CustomerName LIKE &CustomerName
       WHEN NOT null(&CustomerName);

CustomerIDCard >= &CustomerIDCard
       WHEN NOT null(&CustomerIDCard);

The previous code is interpreted as follows:

If the &CustomerName variable has some value, GeneXus will apply the filter: CustomerName LIKE &CustomerName. Besides, if the &CustomerIDCard variable has some value, it will also apply the filter: CustomerIDCard >= &CustomerIDCard, in and AND relation with the other filters.  If no variable has value, no filter will be applied. 

Example with orders

For each CustomerName WHEN NOT null(&CustomerName)
       Order CustomerIDCard WHEN NOT null(&CustomerIDCard)
       ...
EndFor

In this For Each, the navigation will be performed ordered by CustomerName if the &CustomerName variable has some value. If it does not have any value, the order will be by CustomerIDCard if &CustomerIDCard has some value. If none of the previous conditions occur, the navigation will be performed in an undefined order.

For each CustomerName WHEN NOT null(&CustomerName)
       Order CustomerIDCard WHEN NOT null(&CustomerIDCard)
       Order CustomerId
       ...
EndFor

In the For Each in the example, the navigation orders are the same as those in the previous one. In the case where none of the Conditions occur because there is an unconditional Order, the navigation is performed by the CustomerId unconditional Order.

Scope

See also

For Each command
Xfor Each command
Xfor First command
Like operator