The effect of using triggers to evaluate virtual fields in LANSA for iSeries and LANSA Open

Product/Release:LANSA for iSeries & LANSA Open
Abstract:A SELECT with a WHERE condition is evaluated slightly differently on LANSA for iSeries and LANSA Open (and LANSA Client)
Submitted By:LANSA Technical Support

A difference exists in the way that a select command with a where condition works between LANSA for iSeries and LANSA Open (or LANSA Client). In this particular case a trigger was being used to evaluate a virtual field, which is possible, but not something generally recommended.

For example, say a virtual field has been defined eg. #V1 and this virtual field has a trigger function to perform an after read operation. Lets say that this trigger performs a very simple operation such as CHANGE FIELD(#V1) TO(1)

If a SELECT…WHERE(#V1 *EQ 1) is performed in LANSA for iSeries the WHERE condition is performed in the function and not in the I/O module. The trigger function is, however, executed in the I/O module. In simple terms this means that the SELECT will be executed in the I/O module including the trigger function logic before the WHERE condition is performed in the function. This means also that #V1 will have a value of 1 (because the trigger function has been executed in the I/O module) when the WHERE condition is evaluated.

If a similar command is run from LANSA Open (or LANSA Client)


LceRequestSelectWhere (iSession, "FIELD1,FIELD2", "FILE1", "FIELD1", "#V = 1", FALSE) this logic will not return any values.

The reason is explained in the LANSA Open manual under the LceRequestSelectWhere command:

"If the WHERE expression includes one or more virtual fields, the expression is not evaluated until all the virtual logic has been executed".

So, in LANSA Open, the WHERE condition is evaluated in the I/O module, after performing the virtual fields logic but most importantly BEFORE invoking the after read trigger function. In this case the trigger will be fired after the WHERE condition is evaluated which means that the virtual field #V1 will be changed to 1 after the Select(Where) is performed and therefore incorrect values will be returned.

So a SELECT with a WHERE condition may perform differently in LANSA for iSeries than LANSA Open due to when the WHERE condition is evaluated.

This cannot be seen as a defect in either LANSA for iSeries or LANSA Open but merely as a consequence of the way the WHERE condition is evaluated on different platforms. It reinforces the recommendation that users should not evaluate virtual fields using trigger functions. Virtual fields should be derived using the standard methods or via RPG derivation.

There are some alternative solutions for correcting this anomaly:

  1. Define the virtual field using Substringing or RPG derivation.
  2. Use the condition on the client function after the LceRequestSelectWhere command ie. first perform the select and then insert the 'If condition' logic inside the receive loop.