- Equivalence
- Filter (syntaxes 2)
- Exiting from FOR EACH loop
- Influence of the mode for exiting from the loop on the automatic browse of data files
- Running the next iteration
- Modifying the key used for the automatic browse of data files or queries
FOR EACH/FOR ALL statement (browse of files) In french: POUR TOUT / POUR TOUS
Not available with this kind of connection
The FOR EACH statement is used to perform different types of HFSQL browse: - Full browse (according to a specified key or not)
- Browse with filter (simple filter, filter on a key or filter on the search key). In this case, the filters accept the operators of HFilter.
The browse operations can be performed on a data file, a view, a query or a data source. 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 by FOR ALL.
Versions 18 and later New in version 18 Versions 21 and later New in version 21 Remark: From version 19, HFSQL is the new name of HyperFileSQL.
// 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 // Browse 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 // Browse 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 // Browse 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 // Browse 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
// Browse 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 browse Hide the details
1. Full browse according to the best search key
Caution: Modifying the file in the analysis (adding a key item for example) can modify the search key used. FOR EACH <File> [[WITHOUTSAVEPOSITION,] [<Direction>]] ... END 2. Full browse according to the key passed in parameter FOR EACH <File> ON <Key item> [[WITHOUTSAVEPOSITION,] [<Direction>]] ... END
<FOR EACH>: Marks the beginning of statement block. <File>: Name of the HFSQL data file, view or query to browse. <ON>: Defines the type of browse. <Key item>: Name of the key item used to browse the HFSQL data file (view or query). <WITHOUTSAVEPOSITION>: Versions 24 and laterOptional keyword. Disables the position save and restore during the browse. 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. New in version 24Optional keyword. Disables the position save and restore during the browse. 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. Optional keyword. Disables the position save and restore during the browse. 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 browse direction:
| | FromBeginning (default value) | Browses the data file from the first element to the last one. | FromEnd | Browses the data file from the last element to the first one. |
<END>: Marks the end of statement block.
2 - Browse with filter Hide the details
1. Browse with filter (the best search key is automatically defined)
Caution: Modifying the file in the analysis (adding a key item for example) can modify the search key used. FOR EACH <File> WHERE "<1st Condition> [AND/OR/NOT <2nd Condition> [ET/OU/PAS...<Nth Condition>]]" [[WIHTOUTSAVEPOSITION,] [<Direction>]] ... END 2. Browse 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 EACH>: Marks the beginning of statement block. <File>: Name of the HFSQL data file, view or query to browse. <WITH>: Defines the type of browse. <Nth Condition>: Nth condition of the filter for the HFSQL browse. See the Remarks for more details. <AND/OR/NOT>: Optional logical operators used to combine the different filter conditions. <WITHOUTSAVEPOSITION>: Versions 24 and laterOptional keyword. Disables the position save and restore during the browse. 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. New in version 24Optional keyword. Disables the position save and restore during the browse. 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. Optional keyword. Disables the position save and restore during the browse. 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 browse direction:
| | FromBeginning (default value) | Browses the data file from the first element to the last one. | FromEnd | Browses the data file from the last element to the first one. |
<Key item>: Item corresponding to a key of the data file. The filter will be performed on this key. <END>: Marks the end of statement block.
3 - Browse 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 EACH>: Marks the beginning of statement block. <File>: Name of the HFSQL data file, view or query to browse. <WITH>: Defines the type of browse. <Key item>: Key item of the data file to browse. <Value>: Comparison value of the key item. <Minimum Value>, <Maximum Value>: Comparison value of the key item. <WITHOUTSAVEPOSITION>: Versions 24 and laterOptional keyword. Disables the position save and restore during the browse. 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. New in version 24Optional keyword. Disables the position save and restore during the browse. 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. Optional keyword. Disables the position save and restore during the browse. 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 browse direction:
| | FromBeginning (default value) | Browses the data file from the first element to the last one. | FromEnd | Browses the data file from the last element to the first one. |
<END>: Marks the end of statement block.
4 - Browse with "Start with" generic search, ascending or descending Hide the details
FOR EACH <File> WHERE <Key item> [= <Beginning of sought value> [[WITHOUTSAVEPOSITION,] [<Direction>]] ... END
<FOR EACH>: Marks the beginning of statement block. <File>: Name of the HFSQL data file, view or query to browse. <WITH>: Defines the type of browse. <Key item>: Key item of the data file to browse. <Beginning of sought value>: Comparison value of the key item. <WITHOUTSAVEPOSITION>: Versions 24 and laterOptional keyword. Disables the position save and restore during the browse. 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. New in version 24Optional keyword. Disables the position save and restore during the browse. 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. Optional keyword. Disables the position save and restore during the browse. 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 browse direction:
| | FromBeginning (default value) | Browses the data file from the first element to the last one. | FromEnd | Browses the data file from the last element to the first one. |
<END>: Marks the end of statement block.
5 - Browse 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 EACH>: Marks the beginning of statement block. <File>: Name of the HFSQL data file or view to browse. <WITH>: Defines the type of browse. <Composite key>: Composite key of the data file to browse. <Value of Component N>: Value of the component. <WITHOUTSAVEPOSITION>: Versions 24 and laterOptional keyword. Disables the position save and restore during the browse. 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. New in version 24Optional keyword. Disables the position save and restore during the browse. 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. Optional keyword. Disables the position save and restore during the browse. 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 browse direction:
| | FromBeginning (default value) | Browses the data file from the first element to the last one. | FromEnd | Browses the data file from the last element to the first one. |
<END>: Marks the end of statement block. Remarks - The syntax FOR EACH <File> WHERE <Item> = <Value> is equivalent to a browse performed on the data file by HReadSeek. This browse takes the filters previously defined on the data file into account.
- The syntax FOR EACH <File> WHERE <Condition> is used to define a filter and to browse the data file. This browse ignores the filters that were defined beforehand.
- When using queries, the "FOR EACH" syntax may be slower than the "WHILE NOT HOut" syntax. Indeed, when using FOR EACH, an operation for saving/restoring context is automatically performed (equivalent to HSavePosition/HRestorePosition).
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@"]'com'
- Comparisons between strings are performed according to the ASCII value of the characters and not according to the lexicographic value ('a' > 'Z').
- The binary memos and the composite keys cannot be part of an <Item Value>.
- If <Item Value> contains a simple quote (or a double quote), this simple quote (or double quote) must be preceded by a backslash character.
Exiting from FOR EACH loop Several statements are available: - RETURN: Exit from the FOR EACH loop and exit from the current process (or procedure).
- RESULT: Return a status report to the calling process. Exit from the FOR EACH loop and exit from the current process (or procedure).
- BREAK: Exits from the FOR EACH loop and runs the rest of the current process.
Close is used to exit from the FOR EACH loop and to close the current window. Caution: RETURN and RESULT cannot be used in the same process. Influence of the mode for exiting from the loop on the automatic browse of data files - If the browse ends automatically:
- The position in the data file before the browse is restored.
- The possible filter required for the browse is disabled.
- If the browse is interrupted (BREAK, RETURN, RESULT, Close, ...):
- The position in the data file before the browse is not restored.
- The possible filter required for the browse is not disabled. It must be disabled manually (HDeactivateFilter).
Versions 24 and laterIf the keyword WIHTOUTSAVEPOSITION has been used, the position save and restore operations performed during the browse 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. New in version 24If the keyword WIHTOUTSAVEPOSITION has been used, the position save and restore operations performed during the browse 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. If the keyword WIHTOUTSAVEPOSITION has been used, the position save and restore operations performed during the browse 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 next iteration, use the statement Continue:
// Browse 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 browse of data files or queries If the value of the search item is modified when browsing a data file (or a query), some records may be browsed several times. Indeed, modifying the browse item updates the file index key. This modification is taken into account during the automatic reading of the next records. This remark is also valid for the automatic browse of a sorted query (ORDER BY) without search key.
// Browse FileName on 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 // positions on the record after in the browse order // the record will be read again during the browse. ... END
This page is also available for…
|
|
|
| |
| | https://forum.pcsoft.fr/fr-FR/pcsoft.br.windev/3133-winformatica-example-hexecutesqlquery-with-break-and-filter-custom/read.awp |
|
|
|
| |
| |
| |
|
| | https://youtu.be/DY-OWbprP5w
https://windevdesenvolvimento.blogspot.com/2019/03/dicas-2055-windev-webdev-mobile.html
// BTN_PERCORRER_tABELA_FOR_eCAH FOR EACH cliente where "clienteid>"+3 Trace(cliente.nome) END Info("FINALIZADO LEITURA") |
|
|
|
| |
| |
| |
|
| | "When using queries, the "FOR EACH" syntax may be slower than the "WHILE NOT HOut" syntax. Indeed, when using FOR EACH, an operation for saving/restoring context is automatically performed (equivalent to HSavePosition/HRestorePosition). "
This text is not true at all, On my tests using While Not Hout is much more slower than using For Each using a query
Tests: Hout 55 seconds For Each 24 seconds |
|
|
|
| |
| |
| |
|
| NA AULA DE HOJE VOU MOSTRAR FOR EACH On |
|
| PROCEDURE duplica_nota_003_item(_id_numero_nota,_id_numero_nota_anterior)
FOR EACH nota_fiscal_iten WHERE "nota_fiscal_iten.Id_nota_fiscal="+_id_numero_nota_anterior on nota_fiscal_iten.sequencia_digitacao FromBeginning nota_fiscal_iten.Id_nota_fiscal=_id_numero_nota HAdd(nota_fiscal_iten) END
// BLOG COM VIDEO E EXEMPLO
http://windevdesenvolvimento.blogspot.com.br/2017/07/aula-1197-windev-064-tabela-for-each-on.html
https://www.youtube.com/watch?v=sNtAKHGXHac
|
|
|
|
| |
| |
| |
|
| I'll show you how to read a table |
|
| Nessa aula de hoje Vou mostrar como ler uma tabela e pegar a descrição e jogar para dentro de uma string
In this class today I'll show you how to read a table And pick up the description and play inside Of a string
//Blog com video e exemplo
https://forum.pcsoft.fr/pt-BR/pcsoft.br.windev/2233-aula-1105-windev-tabela-53-ler-tabelapegar-descricaocolocar/read.awp
http://windevdesenvolvimento.blogspot.com.br/2017/03/aula-1105-windev-tabela-53-ler.html
https://www.youtube.com/watch?v=CL5SsK_t8Wk
|
|
|
|
| |
| |
| |
|
| I'll show you how to get an edt text/And separate each row with cr |
|
| Nessa aula de hoje vou mostrar como pegar um edt texto e separar cada linha com cr e adicionar numa tabela
In this class today I'll show you how to get an edt text And separate each row with cr And add in a table
// Comandos
TableDeleteAll(TABLE_Texto) // Limpando dados da tabela antes de preenche-la // Clearing Table Data Before Filling It
FOR EACH STRING s_LINHA OF EDT_Texto SEPARATED BY CR // Vou percorrer a string edt texto separando por Enter=Cr // I'll go through the edt text string separating by Enter = Cr TableAddLine(TABLE_Texto,s_LINHA) // Estou adicionando a tabela, a linha encontrada // I am adding the table, the row found END
// Blog com video e exemplo
http://windevdesenvolvimento.blogspot.com.br/2017/03/aula-1104-windev-string-37-separar.html
https://www.youtube.com/watch?v=JOYmP_8OEUE
|
|
|
|
| |
| |
| |
|
| | n_id_nota_fiscal is int=TABLE_nota_fiscal.COL_Id_nota_fiscal Trace("Da Data Menor Vencimento Para Maior") FOR EACH duplicata_receber WHERE "duplicata_receber.Id_nota_fiscal="+n_id_nota_fiscal on duplicata_receber.id_duplicata_receber d_data_vencimento is Date=duplicata_receber.data_vencimento Trace("Data Vencimento: "+d_data_vencimento..Day+"/"+d_data_vencimento..Month+"/"+d_data_vencimento..Year) END
Trace("Da Data Maior Vencimento Para Menor")
FOR EACH duplicata_receber WHERE "duplicata_receber.Id_nota_fiscal="+n_id_nota_fiscal on duplicata_receber.id_duplicata_receber FromEnd d_data_vencimento is Date=duplicata_receber.data_vencimento Trace("Data Vencimento: "+d_data_vencimento..Day+"/"+d_data_vencimento..Month+"/"+d_data_vencimento..Year) END
//Blog com Video e Exemplo http://windevdesenvolvimento.blogspot.com.br/2016/11/aula-960-curso-windev-tabela-033-tabela.html https://www.youtube.com/watch?v=rWDoezouwJg
|
|
|
|
| |
| |
| |
| |
| |
| |
| | |
|