|
|
READ
|
|
|
ALL
|
|
[MULTI-FETCH-clause]
[RECORDS] [IN] [FILE]
view-name
|
||
BROWSE
|
(operand1) | |||||||
[PASSWORD=operand2]
|
||||||||
[CIPHER=operand3]
|
||||||||
[WITH
REPOSITION]
|
||||||||
| [sequence/range-specification] | ||||||||
[STARTING WITH
ISN=operand4]
|
||||||||
[WHERE
logical-condition]
|
||||||||
statement
|
||||||||
END-READ
|
(structured mode only) | |||||||
[LOOP]
|
(reporting mode only) | |||||||
This document covers the following topics:
For an explanation 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 | RETRY | STORE | UPDATE
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.
This statement causes a processing loop to be initiated.
See also READ Statement (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:
| operand1 |
Number of Records to be Read:
The number of records to be read may be limited by specifying
operand1 (enclosed in parentheses, immediately after
the keyword 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 | To emphasize that all records are to be read, you can
optionally specify the keyword ALL.
|
| MULTI-FETCH-clause | See MULTI-FETCH Clause below. |
| view-name |
View Name:
As view-name, you specify the name of a
view, which must have been defined either within a In reporting mode, view-name is the name
of a DDM
if no |
| PASSWORD |
PASSWORD and CYPHER Clauses:
These clauses are applicable only to Adabas or VSAM databases. They cannot be used with Entire System Server. The The |
| WITH REPOSITION | This option is used to make the READ statement
sensitive for repositioning events. See WITH REPOSITION
Option.
|
| sequence/range-specification | This option specifies the sequence and/or the range of retrieval. See Sequence/Range Specification. |
| STARTING WITH ISN=operand4 |
This clause applies only to Adabas and VSAM databases. Access to Adabas
This clause can be used in conjunction with a
Access to VSAM
This clause can only be used in physical sequence. The value supplied (operand4) represents a VSAM RBA (relative byte address of ESDS) or RRN (relative record number of RRDS), which is to be used as a start value for the read operation. Examples
This clause may be used for repositioning within a
For an example, see the program REASISND below. |
| WHERE logical-condition | See WHERE Clause. |
| END-READ | The Natural reserved keyword END-READ must be used
to end the READ statement.
|
Note:
This clause can only be used for Adabas or
DB2 databases.
|
|
MULTI-FETCH
|
|
|
|
|
For more information, see the section Multi-Fetch Clause (Adabas) in the Programming Guide or Multiple Row Processing (SQL) in the Natural for DB2 part in the Database Management System Interfaces documentation.
Note:
This option can only be applied if the underlying database is
Adabas, VSAM or DL/I.
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.
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:
| READ IN PHYSICAL SEQUENCE |
Note:
|
|---|---|
| READ BY ISN |
Note: |
| READ IN LOGICAL SEQUENCE |
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 Notes:
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 |
This clause only applies to Adabas,
XML databases, VSAM
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.
Notes:
|
| STARTING FROM ... ENDING AT/TO |
The The In order to limit the records to an end-value, you may specify an
|
| THRU/ENDING AT |
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 |
If the keyword The |
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 (e.g. READ .. = 'B' THRU 'A')
does not cause a database error, but just returns no record.
|
An incorrect range results in a database error (e.g. Adabas RC=61), because a value range must not be supplied in descending order. |
If a READ .. DESCENDING is used with start- and
end-value, 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 only be used for Adabas Version 7 (or above), DB2, VSAM or DL/I. |
WHERE
logical-condition
|
The WHERE clause may be used to specify an additional
selection criterion (logical-condition) which is
evaluated after a value has been read and before any
processing is performed on the value (including the
AT BREAK evaluation).
The syntax for a logical-condition is described in the section Logical Condition Criteria (in the Programming Guide).
If a LIMIT statement
or a processing limit is specified in a READ statement containing
a WHERE clause, records which are rejected as a result of the
WHERE clause are not counted against the limit.
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.
| *ISN |
The system variable Notes:
|
|---|---|
| *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