[MULTI-FETCH-clause]
[RECORDS ] [IN ] [FILE ]
view-name
|
||||||||||||||
[PASSWORD= operand2]
|
||||||||||||||
[CIPHER= operand3]
|
||||||||||||||
[WITH
REPOSITION ]
|
||||||||||||||
[sequence/range-specification] | ||||||||||||||
[STARTING WITH
ISN= operand4]
|
||||||||||||||
[[IN ]
SHARED HOLD [MODE =option]]
|
||||||||||||||
[SKIP [RECORDS ] IN
HOLD ] |
||||||||||||||
[WHERE
logical-condition]
|
||||||||||||||
statement | ||||||||||||||
END-READ
|
(structured mode only) | |||||||||||||
LOOP |
(reporting mode only) |
This document covers the following topics:
For explanations of the symbols used in the syntax diagram, see Syntax Symbols.
Related Statements: ACCEPT/REJECT
|
AT BREAK
|
AT START OF DATA
|
AT END OF DATA
|
BACKOUT TRANSACTION
|
BEFORE BREAK PROCESSING
|
GET TRANSACTION DATA
|
DELETE
|
END TRANSACTION
|
FIND
|
HISTOGRAM
|
GET
|
GET SAME
|
LIMIT
|
PASSW
|
PERFORM BREAK PROCESSING
| READLOB
|
RETRY
|
STORE
|
UPDATE
|
UPDATELOB
Belongs to Function Group: Database Access and Update
The READ
statement is used to read records from a database.
The records can be retrieved in physical sequence, in Adabas ISN sequence, or
in the value sequence of a descriptor (key) field. The READ
statement causes a processing loop to be initiated.
See also the following sections in the Programming Guide:
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
operand1
|
C | S | N | P | I | B * | yes | no | ||||||||||||
operand2
|
C | S | A | yes | no | |||||||||||||||
operand3
|
C | S | N | yes | no | |||||||||||||||
operand4
|
C | S | N | P | I | B * | yes | no |
* Format B of
operand1
and
operand4
may be used with a length of
less than or equal to 4.
Syntax Element Description:
Syntax Element | Description | ||
---|---|---|---|
operand1
|
Number of Records to be Read:
The number of records to be read may be limited by specifying
Example: READ (5) IN EMPLOYEES ... MOVE 10 TO CNT(N2) READ (CNT) EMPLOYEES ... For this statement, the specified limit has priority over a limit
set with a If a smaller limit is set with the profile or session parameter
Notes:
|
||
ALL
|
ALL Option:
To emphasize that all records are to be read, you can
optionally specify the keyword The |
||
MULTI-FETCH-clause
|
MULTI-FETCH Clause:
See MULTI-FETCH Clause. |
||
view-name
|
View Name:
As In reporting mode, view-name is the name
of a DDM
if no |
||
PASSWORD=operand2
|
PASSWORD and CIPHER Clauses:
These clauses are applicable only to Adabas databases. They cannot be used with Entire System Server. The The |
||
WITH REPOSITION |
WITH REPOSITION Option:
This option is used to make the |
||
sequence/range-specification
|
Sequence/Range Specification:
This option specifies the sequence and/or the range of retrieval. See Sequence/Range Specification. |
||
STARTING WITH
ISN=operand4
|
STARTING WITH ISN Clause:
This clause applies only to Adabas databases. Access to Adabas
This clause can be used in conjunction with a
Examples
This clause may be used for repositioning within a
For an example, see the program |
||
[[IN] SHARED HOLD
[MODE=option]] |
SHARED HOLD Clause:
Note: This clause can be used to place records being read in a "shared hold" state. A record can be put in shared hold by many users at the same time. As long as a record is in a shared hold state, it is protected from being updated, because it cannot be set into an exclusive hold by parallel users. This ensures data consistency for the record data, as no one can update the record while it is being processed. Especially if the same record is fetched with multiple statements
to read different MU/PE occurrences ( Although such a hold state is an efficient way to protect read
sequences, it is a basic and important matter when to release the record again
from this "soft lock". Since this question depends on individual
application aspects, different options can be selected with the
|
||
MODE Option
|
Hold Period
|
Explanation
|
|
C |
Only at the moment of reading the record. | Ensures only that the record version being read has been committed by the last user who updated the record. This option does not really set a lock in hold state, but checks only that the record is not in exclusive hold by another user at time of read. | |
Q |
Until the next record in a sequence is read. | Releases the record from shared hold when
|
|
S |
Until the logical transaction is terminated. | Releases the record from shared hold when a
logical transaction is terminated with an END TRANSACTION or
BACKOUT TRANSACTION
statement.
|
|
MODE=Q and
MODE=S ensure that the record being read cannot be updated
concurrently by other users until it has been released from hold again.
If the See also Example 8 - SHARED HOLD Clause. |
|||
SKIP RECORDS IN
HOLD |
SKIP RECORDS Clause:
Note: Whenever a record is going to be read with hold, a Natural error
NAT3145 (Adabas response code Although error NAT3145 is surely the right reaction to assure a
"clean data processing", sometimes it might be useful if a record
in hold could be skipped. If it is alright that such a record will not be
processed and the loop processing should continue, the If the If the record is already in hold and the Natural error NAT3145 would occur,
See also Example 9 - SKIP RECORDS Clause. |
||
WHERE
logical-condition
|
WHERE Clause:
The The syntax for a If a |
||
END-READ
|
End of READ Statement:
In structured mode, the Natural reserved keyword
In reporting mode, the Natural statement |
||
LOOP |
Note:
This clause can only be used for Adabas databases.
MULTI-FETCH
|
|
Note:[MULTI-FETCH OF
multi-fetch-factor]
is not evaluated for
database types ADA
and ADA2
. The default processing
mode is applied; see profile parameter MFSET
. When used in
conjunction with database type ADA2
, the MULTI-FETCH
clause is ignored in case Adabas LA or large objects fields are used or a view
size greater than 64KB is defined.
For more information, see the section MULTI-FETCH Clause (Adabas) in the Programming Guide.
Note:
This option can only be applied if the underlying database is
Adabas.
With a WITH REPOSITION
option, you can make a
READ
statement sensitive for repositioning events. This allows you
to reposition to another start value within an active READ
loop.
Processing of the READ
statement then continues with the new start
value.
A repositioning event is triggered by one of two ways when you use a
READ
statement with the WITH REPOSITION
option:
When an ESCAPE TOP
REPOSITION
statement is executed. At execution of an
ESCAPE TOP REPOSITION
statement, Natural makes an instant branch
to the loop begin and performs a restart; that is, the database repositions to
a new record in the file according to the current content of the search value
variable. At the same time, the loop-counter
*COUNTER
is reset to zero.
When a READ
loop tries to fetch the next record from the
database and the value of the system variable
*COUNTER
is 0
.
Note:
If *COUNTER
is set to 0
within the active READ
loop, processing of the current record is
continued; no instant branch to the loop begin is performed. You cannot trigger a reposition event in this fashion
on
Natural for Windows, UNIX and OpenVMS. This functionality has only been
retained for compatibility with earlier versions of Natural for Mainframes.
Therefore, it is not recommended that you use this process.
If the READ
statement has a loop-limit (e.g. READ
(10) EMPLOYEES WITH REPOSITION ..
) and a restart event was triggered,
the loop gets another 10 new records, no matter how many records where already
processed until the repositioning takes place.
If an ESCAPE TOP
REPOSITION
statement is executed, but the innermost loop is
not capable of repositioning (since the WITH REPOSITION
keyword is
not set in the READ
statement or the posted loop statement is
anything else but a READ
), a corresponding runtime error is
issued.
Since the ESCAPE TOP
statement does not allow a
reference, you can only initiate a reposition event if the innermost processing
loop is a READ ..WITH REPOSITION
statement.
A reposition event does not trigger the execution of the
AT START OF DATA
section,
nor does it trigger the re-evaluation of the loop-limit operand (if it is a
variable).
If the search value was not altered, the loop repositions to the same record like at initial loop start.
Three syntax options are available to specify the sequence and/or the range of retrieval.
Syntax Option 1:
[IN ] [PHYSICAL ]
|
[SEQUENCE ]
|
Syntax Option 2:
|
|||||||||||||||||
ISN
|
operand6 | operand7 | |||||||||||||||
Syntax Option 3:
[IN ]
[LOGICAL ]
|
[SEQUENCE ]
|
descriptor | ||||||||||||
|
operand6 | operand7 | ||||||||||||
|
operand6
TO operand7
|
|||||||||||||
|
operand6 |
Notes:
ENDING
AT
, THRU
and TO
may not be used. These
comparators are also valid for the HISTOGRAM
statement.
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
operand5
|
S | A | yes | no | ||||||||||||||||
operand6
|
C | S | A | N | P | I | F | B * | D | T | L | yes | no | |||||||
operand7
|
C | S | A | N | P | I | F | B * | D | T | L | yes | no |
* Format B of
operand6
and
operand7
may be used only with a length
of less than or equal to 4.
Syntax Element Description:
Syntax Element | Description |
---|---|
READ
IN PHYSICAL SEQUENCE
|
Read in Physical Sequence:
This option is used to read records in the order in which they are physically stored in a database.
|
READ BY
ISN
|
Read by ISN:
This option is used to read records in the order of Adabas ISNs
(internal sequence numbers). (Instead of using the keyword
Note: |
READ IN
LOGICAL SEQUENCE
|
Read in Logical Sequence:
This option is used to read records in the order of the values of a descriptor (key) field. If you specify a descriptor, the records will be read in the value sequence of the descriptor. A descriptor, subdescriptor, superdescriptor or hyperdescriptor may be used for sequence control. A phonetic descriptor, a descriptor within a periodic group, or a superdescriptor which contains a periodic-group field cannot be used. If you do not specify a descriptor, the default descriptor as
specified in the DDM (field If the descriptor used for sequence control is defined with null-value suppression (Adabas only), any record which contains a null value for the descriptor will not be read. If the descriptor is a multiple-value field (Adabas only), the same record will be read multiple times depending on the number of values present. Note: |
ASCENDING | DESCENDING | VARIABLE | DYNAMIC
SEQUENCE
|
Ascending/Descending Order:
This clause only applies to Adabas,
XML databases
and SQL databases. In a With this clause, you can determine whether the records are to be read in ascending sequence or in descending sequence.
Note: |
STARTING
FROM ... ENDING AT/TO |
STARTING FROM/ENDING AT Clauses:
The The In order to limit the records to an end value, you may specify an
|
THRU/ENDING AT
|
THRU/ENDING AT Option:
If Internally, to determine the end of the range to be read, Natural
reads one record beyond the end value. If you have left the The |
TO
|
TO Option:
If the keyword |
The following list describes the functional differences between the
usage of the THRU/ENDING
AT
and TO
options.
THRU/ENDING AT | TO |
---|---|
When the READ loop terminates because the end
value has been reached, the view contains the first record
"out-of-range".
|
When the READ loop terminates because the end
value has been reached, the view contains the last record of the specified
range.
|
If a end-value variable is modified during the
READ loop, the new value will be used for end-value check on next
record being read.
|
The end-value variable will only be evaluated at
READ loop start. All further modifications during the
READ loop have no effect.
|
An incorrect range (for example, READ .. = 'B' THRU
'A' ) does not cause a database error, but just returns no
record.
|
An incorrect range results in a database error (for example, Adabas RC=61), because a value range must not be supplied in descending order. |
If a READ .. DESCENDING is used with start and end
values, the start value is used to position in the file, whereas the end value
is used by Natural to check for "end-of-range". Therefore, the
start value is higher than (or equal to) the end value.
|
Since both values are passed to the database, they have to appear in ascending order. In other words, the start value is lower than (or equal to) the end value, no matter if you are reading in ascending or descending order. |
In order to check for range overflow, the descriptor value has to appear in the underlying database view; that is, it must be returned in the record buffer. | The descriptor is not required in the record fields returned. |
The end-value check for an Adabas multi-value field (MU-field) or a sub-/super-/hyper-descriptor is not possible and leads to syntax error NAT0160 at program compilation. | You may specify an end value for MU-fields and sub-/super-/hyper-descriptors. |
Can be used for all databases. | Can be used for all databases. |
Note:
The result of READ
/HISTOGRAM
THRU
/ENDING AT
might differ from the result of
READ
/HISTOGRAM
TO
if Natural and the
accessed database reside on different platforms with different collating
sequences.
The Natural system variables *ISN
and
*COUNTER
are available with the READ
statement.
The format/length of these system variables is P10. This format/length cannot be changed.
The usage of the system variables is illustrated below.
System Variable | Explanation |
---|---|
*ISN |
The system variable |
*COUNTER |
The system variable *COUNTER
contains the number of times the processing loop has been entered.
|
** Example 'REAEX1S': READ (structured mode) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 1 VEHIC-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID 2 MAKE END-DEFINE * LIMIT 3 * WRITE 'READ IN PHYSICAL SEQUENCE' READ EMPLOY-VIEW IN PHYSICAL SEQUENCE DISPLAY NOTITLE PERSONNEL-ID NAME *ISN *COUNTER END-READ * WRITE / 'READ IN ISN SEQUENCE' READ EMPLOY-VIEW BY ISN STARTING FROM 1 ENDING AT 3 DISPLAY PERSONNEL-ID NAME *ISN *COUNTER END-READ * WRITE / 'READ IN NAME SEQUENCE' READ EMPLOY-VIEW BY NAME DISPLAY PERSONNEL-ID NAME *ISN *COUNTER END-READ * WRITE / 'READ IN NAME SEQUENCE STARTING FROM ''M''' READ EMPLOY-VIEW BY NAME STARTING FROM 'M' DISPLAY PERSONNEL-ID NAME *ISN *COUNTER END-READ * END
PERSONNEL NAME ISN CNT ID --------- -------------------- ----------- ----------- READ IN PHYSICAL SEQUENCE 50005800 ADAM 1 1 50005600 MORENO 2 2 50005500 BLOND 3 3 READ IN ISN SEQUENCE 50005800 ADAM 1 1 50005600 MORENO 2 2 50005500 BLOND 3 3 READ IN NAME SEQUENCE 60008339 ABELLAN 478 1 30000231 ACHIESON 878 2 50005800 ADAM 1 3 READ IN NAME SEQUENCE STARTING FROM 'M' 30008125 MACDONALD 923 1 20028700 MACKARNESS 765 2 40000045 MADSEN 508 3
Equivalent reporting-mode example: REAEX1R.
DEFINE DATA LOCAL 1 MYVIEW VIEW OF ... 2 NAME 1 #STARTVAL (A20) INIT <'A'> 1 #ATTR (C) END-DEFINE ... SET KEY PF3 ... READ MYVIEW WITH REPOSITION BY NAME = #STARTVAL INPUT (IP=OFF AD=O) 'NAME:' NAME / 'Enter new start value for repositioning:' #STARTVAL (AD=MT CV=#ATTR) / 'Press PF3 to stop' IF *PF-KEY = 'PF3' THEN STOP END-IF IF #ATTR MODIFIED THEN ESCAPE TOP REPOSITION END-IF END-READ ...
DEFINE DATA LOCAL 1 MYVIEW VIEW OF ... 2 NAME 1 #STARTVAL (A20) INIT <'A'> 1 #ATTR (C) END-DEFINE ... SET KEY PF3 ... READ MYVIEW WITH REPOSITION BY NAME = #STARTVAL INPUT (IP=OFF AD=O) 'NAME:' NAME / 'Enter new start value for repositioning:' #STARTVAL (AD=MT CV=#ATTR) / 'Press PF3 to stop' IF *PF-KEY = 'PF3' THEN STOP END-IF IF #ATTR MODIFIED THEN RESET *COUNTER END-IF END-READ ...
The following program reads records from the EMPLOYEES
file in logical sequential order based on the values of the descriptor
NAME
. A FIND
statement is then issued to the VEHICLES
file using the personnel
number from the EMPLOYEES
file as search criterion. The resulting
report shows the name (read from the EMPLOYEES
file) of each
person read and the model of automobile (read from the VEHICLES
file) owned by this person. Multiple lines with the same name are produced if
the person owns more than one automobile.
** Example 'REAEX2': READ and FIND combination ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 FIRST-NAME 2 NAME 2 CITY 1 VEH-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID 2 MAKE END-DEFINE * LIMIT 10 * RD. READ EMPLOY-VIEW BY NAME STARTING FROM 'JONES' SUSPEND IDENTICAL SUPPRESS FD. FIND VEH-VIEW WITH PERSONNEL-ID = PERSONNEL-ID (RD.) IF NO RECORDS FOUND ENTER END-NOREC DISPLAY NOTITLE (ES=OFF IS=ON ZP=ON AL=15) PERSONNEL-ID (RD.) FIRST-NAME (RD.) MAKE (FD.) (IS=OFF) END-FIND END-READ END
PERSONNEL FIRST-NAME MAKE ID --------------- --------------- --------------- 20007500 VIRGINIA CHRYSLER 20008400 MARSHA CHRYSLER CHRYSLER 20021100 ROBERT GENERAL MOTORS 20000800 LILLY FORD MG 20001100 EDWARD GENERAL MOTORS 20002000 MARTHA GENERAL MOTORS 20003400 LAUREL GENERAL MOTORS 30034045 KEVIN DATSUN 30034233 GREGORY FORD 11400319 MANFRED
** Example 'READSCND': READ (with DESCENDING SEQUENCE) ************************************************************************ DEFINE DATA LOCAL 1 EMPL VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 BIRTH END-DEFINE * READ (10) EMPL IN DESCENDING SEQUENCE BY NAME FROM 'ZZZ' DISPLAY *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) END-READ END
** Example 'REAVSEQ': READ (with VARIABLE SEQUENCE) ************************************************************************ DEFINE DATA LOCAL 1 EMPL VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 BIRTH * 1 #DIR (A1) 1 #STARTVALUE (A20) END-DEFINE * SET KEY PF7 PF8 * INPUT 'Select READ direction' // 'Press' 08T 'PF7' (I) 21T 'to read backward' / 08T 'PF8' (I) 'or' 'ENTER' (I) 21T 'to read forward' * IF *PF-KEY = 'PF7' MOVE 'D' TO #DIR MOVE 'ZZZ' TO #STARTVALUE ELSE MOVE 'A' TO #DIR MOVE 'A' TO #STARTVALUE END-IF * READ (10) EMPL IN VARIABLE #DIR SEQUENCE BY NAME FROM #STARTVALUE DISPLAY *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) END-READ END
DEFINE DATA LOCAL 1 #DIRECTION (A1) INIT <'A'> /* 'A' = ASCENDING 1 #EMPVIEW VIEW OF EMPLOYEES 2 NAME ... END-DEFINE ... READ #EMPVIEW IN DYNAMIC #DIRECTION SEQUENCE BY NAME = 'SMITH' INPUT (AD=O) NAME / 'Press PF7 to scroll in DESCENDING sequence' / 'Press PF8 to scroll in ASCENDING sequence' .. IF *PF-KEY = 'PF7' THEN MOVE 'D' TO #DIRECTION END-IF IF *PF-KEY = 'PF8' THEN MOVE 'A' TO #DIRECTION END-IF END-READ ...
** Example 'REASISND': READ (with STARTING WITH ISN) ************************************************************************ DEFINE DATA LOCAL 1 EMPL VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 BIRTH * 1 #DIR (A1) 1 #STARTVAL (A20) 1 #STARTISN (N8) END-DEFINE * SET KEY PF3 PF7 PF8 * MOVE 'ADKINSON' TO #STARTVAL * READ (9) EMPL BY NAME = #STARTVAL WRITE *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) *COUNTER IF *COUNTER = 5 THEN MOVE NAME TO #STARTVAL MOVE *ISN TO #STARTISN END-IF END-READ * #DIR := 'A' * REPEAT READ EMPL IN VARIABLE #DIR BY NAME = #STARTVAL STARTING WITH ISN = #STARTISN MOVE NAME TO #STARTVAL MOVE *ISN TO #STARTISN INPUT NO ERASE (IP=OFF AD=O) 15/01 *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) // 'Direction:' #DIR // 'Press PF3 to stop' / ' PF7 to go step back' / ' PF8 to go step forward' / ' ENTER to continue in that direction' /* IF *PF-KEY = 'PF7' AND #DIR = 'A' MOVE 'D' TO #DIR ESCAPE BOTTOM END-IF IF *PF-KEY = 'PF8' AND #DIR = 'D' MOVE 'A' TO #DIR ESCAPE BOTTOM END-IF IF *PF-KEY = 'PF3' STOP END-IF END-READ /* IF *COUNTER(0290) = 0 STOP END-IF END-REPEAT END
READ EMPL-VIEW WITH NAME = ... IN SHARED HOLD MODE=Q /* Record in shared hold until next record is read. ... GET EMPL-VIEW *ISN /* The record remains unchanged! ... END-READ
READ EMPL-VIEW WITH NAME = ... /* Records found are put in hold while reading. SKIP RECORDS IN HOLD /* Records already held by other users are skipped ... /* to prevent error NAT3145. UPDATE END TRANSACTION END-READ