FORF |
"For" is used to perform read loops on a table.
(For the allocation loops of a variable, refer to the documentation on the For(variable) instruction).
Syntax 1.
For clé1 [ hint-cl]] [ From clé_deb ] [ To clé_fin ] [ where-cl ] [ With Lock | With Stability ]
..... instructions Next [ clé ]
|
Element |
Description |
Restrictions |
|
clé1 |
Designation of a file key used for the sort order, in one of the following forms:
|
this class designates the abbreviation of the file that needs to be open. |
|
id_clé1 |
variable_name that designates a key |
None. |
|
exp_ind |
Expression entière du nombre de parties de clés utilisées. Par défaut, toute la clé est utilisée. |
0<= exp_ind <=number of key components (at most 8). |
|
hint_cl |
See Hint instruction |
None. |
|
clé_deb |
Scanning start key, in the form of a list of expressions separated by the ';' character, corresponding to the sub-key values. |
The number of expressions is at most equal to the number of key components. |
|
clé_fin |
Scanning end key, in the form of a list of expressions separated by the ';' character, corresponding to the sub-key values. |
The number of expressions is at most equal to the number of key components. |
|
where_cl |
See Where instruction |
None. |
# loading onto the screen of all the enterable fields in a mask
For [AMZ]CODE Where CODMSK=[M]MASQUE & SAIAFF=1 & CODTYP<>"ABS"
If !find([F:AMZ]CODZON,[M]ZONE(0..NOL-1))
[M]ZONE(NOL) = [F:AMZ]CODZON
Call TEXTE([F:AMZ]INTIT,[M]INTIT(NOL)) From OBJDIV
[M]CODCTL(NOL) = [F:AMZ]CODCTL
NOL += 1
Endif
Next
"For" is used to perform read loops on a table based on a given key.
The instruction:
For key From clé_deb To clé_fin Where expr_l....Next
is equivalent to:
Read clé >= clé_debWhile [S]fstat <= 2 and clé <= clé_finIf expr_l....EndifRead NextWend
... but it is much quicker on an Oracle table.
The Next instruction is equivalent to a sequence of "Read clé Next" (or Unlock [FIC] followed by "Readlock" if using the With Lock clause) as long as the key value of the recording read is identical for the number of given key parts. If the key is specified without any details on a number of components, the Next instruction is equivalent to a single Read Next (or Readlock).
When "For" contains a condition on a logical expression, Adonix can be requested to internally perform additional Read Next until the expression is complied with.
It is possible to embed "For" loops on the number of key parts so as to define breaks, provided that the number of its components is complied with.
The keys that can be used in For are:
- the key defined in the last Order By clause (if there is one);
- otherwise, one of the keys defined by the file parameterization.
- one of the keys defined in the Order By clause of the Link (if any) or in a Filter instruction;
- otherwise the key defined in the last Order By clause corresponding to the main file (if there is one);
- otherwise, one of the keys defined by the main file parameterization.
When using the link abbreviation (defined on the occasion of a previous Link) in a "For", Adonix tries to read a recording of the main file on the basis of the key given in the "For" instruction, then a recording in each linked file based on the key and the read mode provided in Link.
Reading a recording, with the details of a key part, automatically positions the [G]currlen variable; in effect, this variable contains the number of the key part being used; Caution, any subsequent read, without any details on the key, will be performed on the same key part. To read an entire key, specify the key or position the [G]currlen variable to the value 0. The value 0 is the default value.
The abbreviation of the file or the key name can be left out of the syntax of For:
- the last key used when accessing the file (if the file has been accessed);
- otherwise the key defined in the last Order By clause (if it has been defined),
- otherwise, the first key defined by the file parameterization.
The reckey variable can be used instead of the key name:
A "For" loop is exited if:
A "For ...loop is not exited With Lock" if a locked recording is found, on the other hand, the [S]fstat variable will pe positioned to 1 right after Next.
Upon exiting a loop, [S]fstat is positioned on 0 except when reaching the end of the file, when there are no longer any recordings verifying the Where clause or a key less than or equal to clé_fin (end_key). In these cases, [S]fstat will be equal to 4.
Upon exiting a loop, the current recording is the last one to be scanned. This is still the case for the syntax with lock, the resulting recording can be locked or not.
The "For ... With Lock" :
With Orcale, the response time mainly depend on the number of queries transmitted to the server. Each Read order generates an SQL query of "select" type. It is the same for each For loop (of the first level). In this case, a kind of index is created on the selected recordings, such making the following accesses nearly instantaneous. Therefore the "For" loops must be used as often as possible.
A query is all the more lengthy since the number of recordings to be scanned is large. The Where clause (either a Filter placed before or on the For order itself) can limit this search.
For instance:
Either a client command file with the abbreviation [CCL] and a key CLICCL whose first two parts are CODCCL et DATCCL. An attempt is made at reading the clients between 2 dates.
If the following action is carried out:
For [CCL]CLICCL(1)
# many recordings will be selected.
[L]CLICUR = [F:CCL]CODCLI
For [CCL]CLICCL
If DATCCL >= DATDEB & DATCCL <= DATFIN
... etc ...
It is necessary to do:
Filter [CCL] Where DATCCL >= DATDEB and DATCCL <= DATFIN# all off-limit orders are deleted from the start.For [CCL]CLICCL(1)For [CCL]CLICCL
In the same fashion (and with the same file):
For [CCL]CLICCL From [L]CLICUR To [L]CLICURand For [CCL]CLICCL Where CODCLIF = [L]CLICUR
are equivalent (and proven) syntaxes.
It is prohibited to use the Where (or From to) clause inside a For loop and concerning the same table. The usual If tests need to be used, if necessary.
Never initiate a transaction by Trbegin (relating to a file of the loop) in a For loop With Lock, because, with Oracle, only one recording could be read.
If the For loop With Lock is carried out inside a transaction, the recordings will only be locked after the Commit. Outside of a transaction, the recording locked by For ... With Lock is locked by Next. This is not the case with Oracle. It should be underlined that locks are a limited resource. Refer to the documentation on the Readlock instruction for further details.
With DB2, there is a limit on the number of fields retrieved by a query. The read of a file exceeding 255 columns generates an error, including the read of a linked file. There are no problems regarding the definiton of the table, since this check is already in place when entering these fields. On the other hand, there is a problem regarding the read based on an abbreviation derived from the Link instruction. The solution consists in filtering the necessary fields via the Columns instruction.
# Column filter on the class derived from the link
Local File ORDERS [ORD]
Local File ITMMASTER [ITM]
Link [ORD] with [ITM]ITM0=[F:ORD]ITMREF as [ORI]
# Position of the filter on the product ref., the product dest., the order no.
Columns [ORI]([ITM]ITMREF,[ITM]ITMDES1,[ORD]WIPNUM)
For [ORI]
...
Next
It should be noted that there is a different behaviour between Oracle and SQL-Server in the For loops, when creating recordings that verify the Where clause and whose key is higher than the current key:
With Oracle, these recordings will never be read
With SQL-Server, they will be read in the loop.
It is thus recommended not to use such algorithms, or at least to prevent the read of the created recordings, using the "with stability" clause. Caution, this "with stability"clause slows down the execution since it uses temporary tables for each query).
example with SQL-Serveur:
Let us consider the table XXX (CH1, CH2, FLG) with 2 lines:
'AAA', 'description 1', 0
'CCC', 'description 2', 1
with the KEY relating to the field CH1
with the key KFL relating to the field FLGFor [XXX]KEY with stability
If CHP1 = 'AAA'
CHP1 = 'BBB'
Rewrite [XXX]
endif
NextUsing a stable cursor, 2 lines can be read: 'AAA, 'CCC'.
Using a non-stable cursor (so, without the stability clause being specified), 3 lines can be read:
'AAA, 'BBB', 'CCC'.For [XXX]KEY with stability
If CHP1 = 'AAA'
CHP1 = 'BBB'
Rewrite [XXX]
endif
NextUsing a stable or non-stable cursor, 2 lines can be read: 'AAA, 'CCC' because the line thus modified is no longer in the normal selection order.
It is now possible to optimize the reads of recordings when the key is comprised of 2 elements. One of the 2 syntaxes is used, relying on the multi-query principle:
The purpose is to simplify the queries by avoiding the "or" of the Where clause in the query.
It should be noted that the syntax Forclé Fromclé_deb Toclé_fin remains in single-query mode and it thus time-consuming.
For instance:
Local File ACCES[ACC]
For [ACC]CODACC From "AAA";"AAA" to "ZZZ";"ZZZ"
Nextgenerates the following query (CODACC being a 2-part key):
Select /*+ INDEX(ACC_ ACCES_CODACC) */ ACC_.ROWID, ACC_.*
From X3.ACCES ACC_
Where ( ( ACC_.USR_0 = :1 And ACC_.CODACC_0 >= :2 )
Or ( ACC_.USR_0 > :1 ) )
And
( ( ACC_.USR_0 = :3 And ACC_.CODACC_0 <= :4 )
Or ( ACC_.USR_0 < :3 ) )
Order by ACC_.USR_0,ACC_.CODACC_0Optimized example:
Local File ACCES[ACC]
For [ACC]CODACC From "AAA";"AAA" to "ZZZ";"ZZZ"
Nextgenerates the following query:
Select /*+ INDEX(ACC_ ACCES_CODACC) */ ACC_.ROWID, ACC_.*
From X3.ACCES ACC_
Where ( ACC_.USR_0 = :1 And ACC_.CODACC_0 >= :2 )
Order by ACC_.USR_0,ACC_.CODACC_0followed by this query:
Select /*+ INDEX(ACC_ ACCES_CODACC) */ ACC_.ROWID, ACC_.*
From X3.ACCES ACC_
Where ( ACC_.USR_0 = :1 And ACC_.CODACC_0 >= :2 )
Order by ACC_.USR_0,ACC_.CODACC_0
By default, the Adonix engine lets the database decide on the most appropriate browsing key for the read. The With Nohint clause becomes the default option.
With SQL-Server, the cursor is unstable by default. The With stability clause makes it stable.
|
Error |
Description |
|
ERCLAS (7) : |
Abbreviation not found. |
|
ERRET (32) |
Wrong loop nesting For (ForCLE(2) followed by ForCLE(1) for instance) or Where clause inside a loop For. |
|
FISLOCK(43) |
Not enough locks (With Lock). |
FORV - BREAK - WHILE - REPEAT - READ - LINK - LOCKWAIT - READLOCK - COLUMNS
CURRIND - CURRLEN - WHERE - HINT
|
Copyright © Sage 1999 - 2007 |