|
|
|
|
|
|
|
|
|
|
- Equivalence
- Filter (syntax 2)
- Exiting from FOR EACH loop
- Influence of the mode for exiting from the loop on the automatic iteration of data files
- Running the next iteration
- Modifying the key used for the automatic iteration of data files or queries
FOR EACH/FOR ALL statement (loop through files) In french: POUR TOUT / POUR TOUS
 Not available with this kind of connection
The FOR EACH statement is used to run different types of HFSQL loops: - Full loop (according to a specified key or not)
- Loop with filter (simple filter, filter on a key or filter on the search key). In this case, the filters accept the operators of HFilter.
You can loop through data files, views, queries or data sources. The records locked in read/write are not read. Remarks: - If the key given to the FOR EACH statement is the key that was previously returned by HFilter, the filter will be respected.
- The FOR ALL, FOR EACH statements are accepted. The FOR EACH statement will be used in this documentation but it can be replaced with FOR ALL.
// Syntax 1
FOR EACH Customer
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END // Syntax 1
FOR EACH Customer ON CustomerNum
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END // Syntax 2
// Loop with filter
FOR EACH Customer where "CustomerCity = 'Montpellier'"
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END
City = "Lyon"
FOR EACH Customer where "CustomerCity = '" + City + "' and CustomerAge >= 21"
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END // Syntax 2
// Loop with filter on a specific key
FOR EACH Customer where "CustomerCity = 'Montpellier'" ON CustomerNum
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END
City = "Lyon"
FOR EACH Customer where "CustomerCity = '" + City + "' AND " + ...
"CustomerAge >= 21" ON CustomerNum
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END // Syntax 3
// Comparison according to a value
FOR EACH Customer where CustomerName = "Doe"
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END
// Comparison according to a value
FOR EACH Orders where OrderDate = "20031231"
// Add orders into the List Box control
ListAdd(LIST_OrderList, Order.OrderNum)
END // Syntax 3
// Comparison according to a set of values
FOR EACH Orders where OrderDate = "20030101" TO "20031231"
// Add orders into the List Box control
ListAdd(LIST_OrderList, Order.OrderNum)
END
// Comparison according to a set of values
FOR EACH Orders where "20030101" <= OrderDate <= "20031231"
// Add orders into the List Box control
ListAdd(LIST_OrderList, Order.OrderNum)
END // Syntax 4
// Loop with filter on a specific key
FOR EACH Customer where CityCustomer [= "Mont" FromEnd
// Add customers into the List Box control
ListAdd(LIST_CustomerList, Customer.CustomerNum)
END // Syntax 5
// Loop with filter on a composite key
FOR EACH ContactFile where CCState = ["Prospect", 69]
// Add contacts into the List Box control
ListAdd(LIST_ContactList, ContactFile.ContactNum)
END
// Loop with filter on a composite key
FOR EACH ContactFile where CZipCode [= ["Prospect", 69]
// Add contacts into the List Box control
ListAdd(LIST_ContactList, ContactFile.ContactNum)
END Syntax
1 - Full loop Hide the details
1. Full loop using the best search key
Caution: Modifying the file during analysis (adding a key item, for example) may modify the browse item used..
FOR EACH <File> [[WITHOUTSAVEPOSITION,] [<Direction>]]
...
END
2. Full loop using the key passed as parameter
FOR EACH <File> ON <Key item> [[WITHOUTSAVEPOSITION,] [<Direction>]]
...
END
<FOR EVERYTHING>: Marks the beginning of the statement block. <File>: Name of the HFSQL data file, view or query to loop through. <ON>: Defines the type of loop. <Key item>: Name of the key item used to loop through the HFSQL data file, view or query. <WITHOUTSAVEPOSITION>: Optional keyword.
Disables the position save and restore features in the loop. Indeed, FOR EACH automatically saves the position at the beginning of the loop and restores this position in case of "normal" exit from the loop (the position is not restored if the loop is interrupted by the keywords BREAK, RETURN or RESULT).
These position save and restore operations can have a high impact (mainly in terms of time), especially if the code using the instruction FOR EACH itself is called in a loop. <Direction>: Optional indicator for the iteration direction:
| | FromBeginning
(default value) | Loops through the data file from the first element to the last. | | FromEnd | Loops through the data file from the last element to the first. |
<END>: Marks the end of the statement block.
2 - Loop with filter Hide the details
1. Loop with filter (the best search key is automatically defined)
Caution: Modifying the file during analysis (adding a key item, for example) may modify the browse item used..
FOR EACH <File> WHERE "<1st Condition>
[AND/OR/NOT <2nd Condition>
[ET/OU/PAS...<Nth Condition>]]" [[WIHTOUTSAVEPOSITION,] [<Direction>]]
...
END
2. Loop with filter according to the specified key
FOR EACH <File> WHERE "<1st Condition>
[AND/OR/NOT <2nd Condition>
[ET/OU/PAS...<Nth Condition>]]" [[WIHTOUTSAVEPOSITION,] [<Direction>]]
...
ON <Key Item>
END
<FOR EVERYTHING>: Marks the beginning of the statement block. <File>: Name of the HFSQL data file, view or query to loop through. <WITH>: Defines the type of loop. <Nth condition>: Nth condition of the filter for the HFSQL loop. For more details, see the Remarks. <AND/OR/NOT>: Optional logical operators used to combine the different filter conditions. <WITHOUTSAVEPOSITION>: Optional keyword.
Disables the position save and restore features in the loop. Indeed, FOR EACH automatically saves the position at the beginning of the loop and restores this position in case of "normal" exit from the loop (the position is not restored if the loop is interrupted by the keywords BREAK, RETURN or RESULT).
These position save and restore operations can have a high impact (mainly in terms of time), especially if the code using the instruction FOR EACH itself is called in a loop. <Direction>: Optional indicator for the iteration direction:
| | FromBeginning
(default value) | Loops through the data file from the first element to the last. | | FromEnd | Loops through the data file from the last element to the first. |
<Key item>: Item corresponding to a key of the data file. The filter will be performed on this key. <END>: Marks the end of the statement block.
3 - Loop with selection filter on the search key Hide the details
1. Comparison filter according to a value
FOR EACH <Fie> WHERE <Key item> [ = / <= / >= ] <Value> [[WIHTOUTSAVEPOSITION,] [<Direction>]]
...
END
2. Comparison filter according to an interval of values
FOR EACH <File> WHERE <Key item> = <Minimal value> TO <Maximum value> [[WITHOUTSAVEPOSITION,] [<Direction>]]
...
END
FOR EACH <File> WHERE <Minimum value> <= <Key item> <= <Maximum value> [[WIHTOUTSAVEPOSITION,] [<Direction>]]
...
END
<FOR EVERYTHING>: Marks the beginning of the statement block. <File>: Name of the HFSQL data file, view or query to loop through. <WITH>: Defines the type of loop. <Key item>: Key item of the data file to loop through. <Value>: Comparison value of the key item. <Minimum value>, <Maximum value>: Comparison value of the key item. <WITHOUTSAVEPOSITION>: Optional keyword.
Disables the position save and restore features in the loop. Indeed, FOR EACH automatically saves the position at the beginning of the loop and restores this position in case of "normal" exit from the loop (the position is not restored if the loop is interrupted by the keywords BREAK, RETURN or RESULT).
These position save and restore operations can have a high impact (mainly in terms of time), especially if the code using the instruction FOR EACH itself is called in a loop. <Direction>: Optional indicator for the iteration direction:
| | FromBeginning
(default value) | Loops through the data file from the first element to the last. | | FromEnd | Loops through the data file from the last element to the first. |
<END>: Marks the end of the statement block.
4 - Loop with "Start with" generic search, ascending or descending Hide the details
FOR EACH <File> WHERE <Key item> [= <Beginning of search value> [[WITHOUTSAVEPOSITION,] [<Direction>]]
...
END
<FOR EVERYTHING>: Marks the beginning of the statement block. <File>: Name of the HFSQL data file, view or query to loop through. <WITH>: Defines the type of loop. <Key item>: Key item of the data file to loop through. <Beginning of search value>: Comparison value of the key item. <WITHOUTSAVEPOSITION>: Optional keyword.
Disables the position save and restore features in the loop. Indeed, FOR EACH automatically saves the position at the beginning of the loop and restores this position in case of "normal" exit from the loop (the position is not restored if the loop is interrupted by the keywords BREAK, RETURN or RESULT).
These position save and restore operations can have a high impact (mainly in terms of time), especially if the code using the instruction FOR EACH itself is called in a loop. <Direction>: Optional indicator for the iteration direction:
| | FromBeginning
(default value) | Loops through the data file from the first element to the last. | | FromEnd | Loops through the data file from the last element to the first. |
<END>: Marks the end of the statement block.
5 - Loop with filter on a composite key Hide the details
1. Exact-match search
FOR EACH <File> WHERE <Composite key> = [<Value of component 1>, ..., <Value of component N>] [[WITHOUTSAVEPOSITION,] [<Direction>]]
...
END
2. Generic search ("Starts with")
FOR EACH <File> WHERE <Composite key> [= [<Value of component 1>, ..., <Value of component N>] [[WITHOUTSAVEPOSITION,] [<Direction>]]
...
END
<FOR EVERYTHING>: Marks the beginning of the statement block. <File>: Name of the HFSQL data file or view to loop through. <WITH>: Defines the type of loop. <Composite key>: Composite key of the data file to loop through. <Value of component N>: Value of the component. <WITHOUTSAVEPOSITION>: Optional keyword.
Disables the position save and restore features in the loop. Indeed, FOR EACH automatically saves the position at the beginning of the loop and restores this position in case of "normal" exit from the loop (the position is not restored if the loop is interrupted by the keywords BREAK, RETURN or RESULT).
These position save and restore operations can have a high impact (mainly in terms of time), especially if the code using the instruction FOR EACH itself is called in a loop. <Direction>: Optional indicator for the iteration direction:
| | FromBeginning
(default value) | Loops through the data file from the first element to the last. | | FromEnd | Loops through the data file from the last element to the first. |
<END>: Marks the end of the statement block.
6 - Browse a file with a condition on a spatial index Hide the details
FOR ALL <Fichier> WITH Fonction_WLangage(File.RubriqueSpatiale, variable_spatiale)
...
END
<FOR EVERYTHING>: Marks the beginning of the statement block. <File>: Name of the data file, view or HFSQL query to be browsed (containing a spatial index). <WITH>: Defines the type of loop. <Function_WLanguage>: Name of a WLanguage function for manipulating geometries. The following functions are available for conditions on a spatial index: GeometryOverlap, GeometryContain, GeometryCover, GeometryCross, GeometryDistance, GeometryEqual, GeometryWithin, GeometryCoveredBy, GeometryIntersect. <RubriqueSpatiale>: Spatial section of the data file. This item must:- be of type "Geographic data" or "Geometric data".
- be defined as a "Spatial key".
<Spatial_variable>: Spatial variable used for comparison with spatial item. This variable can be of type PolygonGeo, Polygon2D, etc. <END>: Marks the end of the statement block. Remarks Equivalence - The syntax FOR EACH <File> WHERE <Item> = <Value> is identical to looping through the data file with HReadSeek. This loop uses the filters previously defined on the data file.
note: crossed-out recordings are taken into account during the run.. - The syntax FOR EACH <File> WHERE <Condition> is used to define a filter and to loop through the data file. This loop ignores the filters that were previously defined.
- When using queries, the "FOR EACH" syntax may be slower than the "WHILE NOT HOut" syntax. When using FOR EACH, the context is automatically saved/restored (equivalent to HSavePosition/HRestorePosition).
Filter (syntax 2) The general syntax of a filter has the following format: "<Item name> <Operators> <Item value>" For example: "CustomerName > 'Doe' and ZipCode = 75 or CustomerAge >= 32" The supported operators depend on the type of items used in the condition: | | | | <> | Different | Valid for all types | | > | Greater than | Valid for all types | | >= | Greater than or equal to | Valid for all types | | < | Less than | Valid for all types | | <= | Less than or equal to | Valid for all types | | = | Strictly equal to | Valid for all types | | ~= | Almost equal to | Valid for the "string" types only | | ] | Contains | Valid for the "string" types only | | ]= | Starts with | Valid for the "string" types only |
Remarks: - Constant strings must be enclosed in single quotes. For example: "CustomerName = '"+Customer+"'"
- <Item name> must only contain letters, digits and underscore characters ("_"). If <Item name> contains other characters (quote, ...), the name of the item must be enclosed in double quotes. For example: "e_mail@"]'en'
- Comparisons between strings are made according to the ASCII value of characters and not in a lexicographical way ('a' > 'Z').
- Binary memos and composite keys cannot be part of an <Item value>.
- If <Item value> contains a single or double quote, the single (or double) quote must be preceded by a backslash.
Exiting from FOR EACH loop Several statements are available: - RETURN: Exit the POUR TOUT loop and exit the current process (or procedure).
- RETURN: Return a status report to the calling process. Exit the POUR TOUT loop and exit the current process (or procedure).
- BREAK: Exit POUR TOUT loop and execute further processing.
Farm is used to exit from the FOR EACH loop and to close the current window. Caution: RETURN and RETURN cannot be used in the same block of code. Influence of the mode for exiting from the loop on the automatic iteration of data files - If the loop ends automatically:
- The position in the data file before the start of the loop is restored.
- Any filter required for the loop is disabled.
- If the route is interrupted (EXIT, RETURN, SEND, CLOSE instructions, etc.):
- The position in the data file before the start of the loop is not restored.
- Any filter required for the loop remains enabled. It must be disabled manually (HDeactivateFilter).
If the keyword WIHTOUTSAVEPOSITION has been used, the position save and restore operations performed in the loop are disabled. Indeed, FOR EACH automatically saves the position at the beginning of the loop and restores this position in case of "normal" exit from the loop (the position is not restored if the loop is interrupted by the keywords BREAK, RETURN or RESULT).
These position save and restore operations can have a high impact (mainly in terms of time), especially if the code using the instruction FOR EACH itself is called in a loop. Running the next iteration To directly run the next iteration without ending the code of the current iteration, use the Continue statement: // Loop through FileName in the order of KeyItem
FOR EACH FileName ON KeyItem
...
IF Condition = True THEN CONTINUE // Return to the FOR EACH keyword
// and go to the next record
...
END Modifying the key used for the automatic iteration of data files or queries If the value of the item is modified when looping through a data file (or a query), some records may be looped through several times. Changing the browse item updates the file index key. This modification is taken into account during the automatic reading of the next records. This is also valid for an automatic loop of a sorted query (ORDER BY) without search key. // Loop through FileName using KeyItem
FOR EACH FileName ON KeyItem
...
IF Condition = True THEN
// Modify the value of the search key
FileName.KeyItem = "New value"
HModify(FileName)
END
// If the new value of the search key
// sets the position on the following record in the iteration order
// the record will be read again during the loop.
...
END
This page is also available for…
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|