For Each Optimizations

Official Content
This documentation is valid for:

When the following patterns appear on a For Each command, a SQL optimized query will be generated.

Optimizations: First 1 record(s)

The following patterns optimize the SQL sentence to get the first record only.

For Each [Order]
[Where ...]
[Defined by ...]
....
Exit
EndFor

or

For Each [Order]
[Where ...]
[Defined by ...]
....
When None
...
EndFor

The Exit command must be set immediately before the EndFor, no comments are allowed between the Exit and EndFor keywords.

Optimizations: count(*)

The following pattern optimizes the SQL sentence to generate a Count(*) aggregation on the data.

&i = 0
For Each
defined by AttName
  &i += 1
EndFor

Optimizations: Sum

The following pattern optimizes the SQL sentence to generate an aggregate operation on the attribute referenced on the For Each block section.

For Each
defined by AttName
  &i = &i + AttId
Endfor

Optimizations: Update

The following pattern optimizes the SQL sentence to generate an Update SQL statement on the data.

For Each
Where <Condition>
  Att1 = <Expression>
  Att2 = <Expression>
Endfor

Optimizations: Delete

The following pattern optimizes the SQL sentence to generate a Delete SQL statement on the data.

For Each
defined by AttName
  delete
Endfor

Considerations

All conditions and filters must be evaluated on the DBMS.

An Expression can include attributes, variables or constants. All the assigned (updated) attributes must belong to the same table.