READ

The number of records to be read may be limited by specifying operand1 (enclosed in parentheses, immediately after the keyword READ) - either as a numeric constant (in the range from 0 to 4294967295) or as the name of a numeric variable.

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 LIMIT statement.

If a smaller limit is set with the profile or session parameter LT, the LT limit applies.

Notes:

1. If you wish to read a 4-digit number of records, specify it with a leading zero: (0nnnn); because Natural interprets every 4-digit number enclosed in parentheses as a line-number reference to a statement.
2. operand1 is evaluated when the READ loop is entered. If the value of operand1 is modified within the READ loop, this does not affect the number of records read.

To emphasize that all records are to be read, you can optionally specify the keyword ALL.

The ALL option is used by default if operand1 and ALL are omitted.

See MULTI-FETCH Clause below.

As view-name, you specify the name of a view, which must have been defined either within a DEFINE DATA statement or outside the program in a global or local data area.

In reporting mode, view-name is the name of a DDM if no DEFINE DATA LOCAL statement is used.

CIPHER=operand3

These clauses are applicable only to Adabas databases. They cannot be used with Entire System Server.

The PASSWORD clause is used to provide a password when retrieving data from a file which is password-protected.

The CIPHER clause is used to provide a cipher key when retrieving data from a file which is enciphered.

See the statements FIND and PASSW for further information.

This option is used to make the READ statement sensitive for repositioning events. See WITH REPOSITION Option.

This option specifies the sequence and/or the range of retrieval. See Sequence/Range Specification.

This clause applies only to Adabas databases.

This clause can be used in conjunction with a READ statement in physical or in logical (ascending/descending) sequence. The value supplied (operand4) represents an Adabas ISN (Internal Sequence Number) and is used to specify a definite record where to start the READ loop.

• Logical Sequence
  Even if documented with an equal character (=), the READ statement does not return only those records with exactly the start value in the corresponding descriptor field, but starts a logical browse in ascending or descending order, beginning with the start value supplied. If some records have the same contents in the descriptor field, they will be returned in an ISN-sorted sequence.

  The STARTING WITH ISN clause is some kind of a "second level" selection criterion that applies only if the start value matches the descriptor value for the first record. All records with a descriptor value that is the same as the start value and an ISN that is "less equal"("greater equal" for a descending READ) than the start ISN are ignored by Adabas. The first record returned in the READ loop is either

  • the first record with descriptor = start value and an ISN "greater" ("less" for a descending READ) than the start ISN,

  • or if such a record does not exist, the first record with a descriptor "greater" ("less" for a descending READ) than the start value.

• Physical Sequence
  The records are returned in the order in which they are physically stored. If a STARTING WITH ISN clause is specified, Adabas ignores all records until the record with the ISN that is the same as the start ISN is reached. The first record returned is the next record following the ISN=start ISN record.

This clause may be used for repositioning within a READ loop whose processing has been interrupted, to easily determine the next record with which processing is to continue. This is particularly useful if the next record cannot be identified uniquely by any of its descriptor values. It can also be useful in a distributed client/server application where the reading of the records is performed by a server program while further processing of the records is performed by a client program, and the records are not processed all in one go, but in batches.

For an example, see the program REASISND below.

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 (GET SAME statement) or to browse over a LOB field in a piecemeal technique (READLOB statement), the shared hold state can guarantee data stability over this transaction without blocking the record for other users.

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 subclause.

• the next record is read in the loop sequence or

• the loop is terminated or

• an END TRANSACTION or BACKOUT TRANSACTION is executed.

If the MODE subclause is not specified, MODE=C is the default.

See also Example 8 - SHARED HOLD Clause below.

Whenever a record is going to be read with hold, a Natural error NAT3145 (Adabas response code 145) might happen if the record is in hold by another user at this time. This occurs if a shared hold is requested and the record is in exclusive hold or if an exclusive hold is requested and the record is in either exclusive or shared hold.

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 SKIP RECORDS clause should be used.

If the SKIP RECORDS clause is applied, Natural first tries to read the record with hold.

If the record is already in hold and the Natural error NAT3145 would occur,

• no error processing is initiated;

• the record (currently in hold by another user) is instantly re-fetched without hold, but not processed in terms of the program logic;

• the record which comes next after the skipped record is read with hold and the processing continues.

See also Example 9 - SKIP RECORDS Clause.

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.

In structured mode, the Natural reserved keyword END-READ must be used to end the READ statement.

In reporting mode, the Natural statement LOOP is used to end the READ statement.

This option is used to read records in the order in which they are physically stored in a database.

PHYSICAL is the default sequence.

This option is used to read records in the order of Adabas ISNs (internal sequence numbers). (Instead of using the keyword BY, you may specify the keyword WITH, which would have the same effect).

READ BY ISN can only be used for Adabas databases.

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 Default Sequence) will be used.

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.

This clause only applies to Adabas, XML databases and SQL databases. In a READ PHYSICAL statement, it can only be applied to DB2 databases.

With this clause, you can determine whether the records are to be read in ascending sequence or in descending sequence.

• The default sequence is ascending (which may, but need not, be explicitly specified by using the keyword ASCENDING).

• If the records are to be read in descending sequence, you specify the keyword DESCENDING.

• If, instead of determining it in advance, you want to have the option of determining at runtime whether the records are to be read in ascending or descending sequence, you either specify the keyword VARIABLE or DYNAMIC, followed by a variable (operand5). operand5 has to be of format/length A1 and can contain the value A (for "ascending") or D (for "descending").

  • If keyword VARIABLE is used, the reading direction (value of operand5) is evaluated at start of the READ processing loop and remains the same until the loop is terminated, regardless if the operand5 field is altered in the READ loop or not.

  • If keyword DYNAMIC is used, the reading direction (value of operand5) is evaluated before every record fetch in the READ processing loop and may be changed from record to record. This allows to change the scroll sequence from ascending to descending (and vice versa) at any place in the READ loop.

The STARTING FROM and ENDING AT clauses are used to limit reading to a set of records based on a user-specified range of values.

The STARTING FROM clause (= or EQ or EQUAL TO or [STARTING] FROM) determines the starting value for the read operation. If a starting value is specified, reading will begin with the value specified. If the starting value does not exist in the file, the next higher (or lower for a DESCENDING read) value will be used. If no higher (or lower for DESCENDING) value exists, the loop will not be entered.

In order to limit the records to an end-value, you may specify an ENDING AT clause with the terms THRU, ENDING AT or TO, that imply an inclusive range. Whenever the read descriptor field exceeds the end-value specified, an automatic loop termination is performed. Although the basic functionality of the TO, THRU and ENDING AT keywords looks quite similar, internally they differ in how they work.

If THRU or ENDING AT is used, only the start-value is supplied to the database, but the end-value check is performed by the Natural runtime system, after the record is returned by the database. If the read direction is ASCENDING, you have to supply the lower value as the start-value and the higher-value as the end-value, since the start-value represents the value (and record) returned first in the READ loop. However, if you invoke a backwards read (DESCENDING), the higher value has to appear in the start-value and the lower-value in the end-value.

Internally, to determine the end of the range to be read, Natural reads one record beyond the end-value. If you have left the READ loop because the end-value has been reached, be aware that this last record is in fact not the last record within the demanded range, but the first record beyond that range (except if the file does not contain a further record after the last result record).

The THRU and ENDING AT clauses can be used for all databases which support the READ or HISTOGRAM statements.

If the keyword TO is used, both the start-value and the end-value are sent to the database, and Natural does not perform checks for value ranges. If the end-value is exceeded, the database reacts the same as when "end-of-file" is reached, and the database loop is exited. Since the complete range checking is done by the database, the lower-value (of the range) is always supplied in the start-value and the higher-value filled into the end-value, regardless if you are browsing in ASCENDING or DESCENDING order.

The system variable *ISN contains the Adabas ISN of the record currently being processed.