When the following patterns appear on a For Each command, a SQL optimized query will be generated.
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 ...]
....
Exit
When None
...
EndFor
The Exit command must be set immediately before the EndFor or When None, no comments are allowed between the Exit and EndFor/When None keywords.
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
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
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
The following pattern optimizes the SQL sentence to generate a Delete SQL statement on the data.
For Each
defined by AttName
delete
Endfor
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.