This document covers the following topics:
Related Statements: ACCEPT/REJECT
|
AT BREAK
| AT START OF DATA
| AT END OF DATA
| BACKOUT TRANSACTION
| BEFORE
BREAK PROCESSING
| DELETE
| END
TRANSACTION
| GET
|
GET SAME
|
GET TRANSACTION
| HISTOGRAM
| LIMIT
| PASSW
| PERFORM BREAK
PROCESSING
| READ
|
READLOB
|
RETRY
| STORE
| UPDATE
|UPDATELOB
Belongs to Function Group: Database Access and Update
The FIND
statement is used to select a set of records from the database
based on search criteria consisting of fields defined as descriptors (keys).
This statement causes a processing loop to be initiated and then executed for each record
selected. Each field in each record may be referenced within the processing loop. It is
not necessary to issue a READ
statement
following the FIND
in order to reference the fields within each record
selected.
See also the following sections in the Programming Guide:
The Natural system variables *ISN
, *NUMBER
, and
*COUNTER
are automatically created for each
FIND
statement issued. A reference number must be supplied if the system
variable was referenced outside the current processing loop or through a FIND UNIQUE
, FIND FIRST
, or FIND NUMBER
statement. The
format/length of each of these system variables is P10; this format/length cannot be
changed.
System Variable | Availability/Usage |
---|---|
*ISN |
|
*NUMBER |
See system variable *NUMBER in the System Variables documentation. With Entire System Server, |
*COUNTER |
The system variable *COUNTER contains the number of
times the processing loop has been entered.
|
See also Example 13 - Using System Variables with the FIND Statement.
Multiple FIND
statements may be issued to create nested loops whereby an
inner loop is entered for each record selected in the outer loop.
See also Example 14 - Multiple FIND Statements.
With Entire System Server, FIND
NUMBER
and FIND
UNIQUE
as well as the PASSWORD
, CIPHER
, COUPLED
and RETAIN
clauses are not permitted.
FIND
|
[MULTI-FETCH-clause] [RECORDS ] [IN ] [FILE ]
view-name
|
|||||||||||
[PASSWORD= operand2]
|
||||||||||||
[CIPHER= operand3]
|
||||||||||||
[WITH ]
[[LIMIT ] (operand4)]
basic-search-criteria
|
||||||||||||
[COUPLED-clause] 4/42 | ||||||||||||
[STARTING WITH
ISN= operand5]
|
||||||||||||
[SORTED-BY-clause] | ||||||||||||
[RETAIN-clause] | ||||||||||||
[[IN ] SHARED HOLD
[MODE =option]]
|
||||||||||||
[SKIP [RECORDS ] IN
HOLD ]
|
||||||||||||
[WHERE-clause] | ||||||||||||
[IF-NO-RECORDS-FOUND-clause] | ||||||||||||
statement | ||||||||||||
END-FIND
|
(structured mode only) | |||||||||||
LOOP |
(reporting mode only) |
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
FIND
|
[RECORDS ] [IN ]
[FILE ]
view-name
|
|||||||||||
[PASSWORD= operand2]
|
||||||||||||
[CIPHER= operand3]
|
||||||||||||
[WITH ] [[LIMIT ] (operand4)]
basic-search-criteria
|
||||||||||||
[COUPLED-clause] 4/42 | ||||||||||||
[SORTED-BY-clause] | (only for FIND FIRST) | |||||||||||
[RETAIN-clause] | ||||||||||||
[WHERE-clause] |
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
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 | |||||||||||
operand5
|
C | S | N | P | I | B* | yes | no |
* Format B of operand1
,
operand4
and
operand5
may be used only with a length of
less than or equal to 4.
Syntax Element Description:
Syntax Element | Description | ||
---|---|---|---|
ALL/operand1 |
Processing Limit:
The number of records to be processed from the selected set may be limited by
specifying
If you specify a limit with FIND (5) IN EMPLOYEES WITH ... MOVE 10 TO #CNT(N2) FIND (#CNT) EMPLOYEES WITH ... For this statement, the specified limit has priority over a limit set with a
If a smaller limit is set with the Notes:
|
||
FIND FIRST | FIND
NUMBER | FIND UNIQUE |
FIND FIRST, FIND NUMBER, FIND UNIQUE Option:
These options are used
|
||
MULTI-FETCH-clause |
MULTI-FETCH Clause:
For Adabas databases, Natural offers a |
||
view-name |
View Name:
The name of a view as defined either within a In reporting mode, view-name is the name of a
DDM if
no |
||
PASSWORD=operand2 |
PASSWORD Clause:
The The If the password is specified as a constant, the If the The password value must not be changed during the execution of a processing loop. See also Example 1 - PASSWORD Clause. |
||
CIPHER=operand3 |
CIPHER Clause:
The The The cipher key may be specified as a numeric constant with 8 digits or as a user-defined variable with format/length N8. If the cipher key is specified as a constant, the The value of the cipher key must not be changed during the processing of a loop
initiated by a See also Example 2 - CIPHER Clause. |
||
WITH LIMIT
operand4
basic-search-criteria
|
WITH Clause:
The The following database-specific considerations apply.
The number of records to be selected as a result of a Note: |
||
COUPLED-clause |
COUPLED Clause:
This clause may be used to specify a search which involves the use of the Adabas coupling facility. See COUPLED Clause. |
||
STARTING WITH ISN=operand5
|
STARTING WITH Clause:
This clause may be used for repositioning within a |
||
SORTED-BY-clause |
SORTED BY Clause:
This clause may be used to cause Adabas to sort the selected records based on the sequence of one to three descriptors. See SORTED BY Clause. |
||
RETAIN-clause |
RETAIN Clause:
This clause may be used to retain the result of an extensive search in large files for further processing. See RETAIN Clause. |
||
[[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 15 - SHARED HOLD Clause below. |
|||
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 a Natural error NAT3145 would occur,
See also Example 16 - SKIP RECORDS Clause. |
||
WHERE-clause |
WHERE Clause:
This clause may be used to specify an additional selection criterion
( |
||
IF-NO-RECORDS-FOUND-clause |
IF NO RECORDS FOUND Clause:
This clause may be used to cause a processing loop initiated with a
|
||
END-FIND |
End of FIND Statement:
In structured mode with processing loop, the Natural reserved keyword
In reporting mode with processing loop, the Natural statement |
||
LOOP |
The FIND FIRST
statement may be used to select and process the first
record which meets the WITH
and
WHERE
criteria.
For Adabas databases, the record processed will be the record with the lowest Adabas ISN from the set of qualifying records.
This statement does not initiate a processing loop.
FIND FIRST
can only be used in reporting mode.
FIND FIRST
is not available for SQL databases.
The following Natural system variables are available with the FIND FIRST
statement:
System Variable | Explanation |
---|---|
*ISN
|
The system variable *ISN contains the Adabas ISN of the
selected record. *ISN will be zero if no record is
found after the evaluation of the WITH and WHERE
criteria.
|
*NUMBER
|
The system variable *NUMBER contains the number of
records found after the evaluation of the WITH criterion and before
evaluation of any WHERE criteria.
*NUMBER will be zero if no record meets the
WITH criterion.
|
*COUNTER
|
The system variable *COUNTER contains 1 if
a record was found; contains 0 if no record was found.
|
Example of FIND FIRST
Statement: See the program FNDFIR
(reporting mode)
The FIND NUMBER
statement is used to determine the number of records which
satisfy the WITH
/WHERE
criteria specified. It does
not result in the initiation of a processing loop and no data fields from
the database are made available.
Note:
Use of the WHERE
clause
may result in significant overhead.
The WHERE
clause can only
be used in reporting mode.
FIND NUMBER
is not available with Entire System Server.
The following Natural system variables are available with the FIND NUMBER
statement:
System Variable | Explanation |
---|---|
*NUMBER
|
The system variable *NUMBER contains the number of
records found after the evaluation of the WITH criterion.
|
*COUNTER
|
The system variable *COUNTER contains the number of
records found after the evaluation of the WHERE criterion.
|
Example for FIND NUMBER
: See the program FNDNUM
(reporting mode).
The FIND UNIQUE
statement may be used to ensure that only one record is
selected for processing. It does not result in the initiation of a processing
loop. If a WHERE
clause is specified, an automatic internal processing loop
is created to evaluate the WHERE
clause.
If no records or more than one record satisfy the criteria, an error message will be
issued. This condition can be tested with the ON ERROR
statement.
FIND UNIQUE
can only be used in reporting mode.
FIND UNIQUE
is not available with Entire System Server.
For SQL databases, FIND UNIQUE
cannot be used. (Exception: On
mainframe computers, FIND UNIQUE
can be used for primary keys; however,
this is only permitted for compatibility reasons and should not be used.)
System Variable | Explanation |
---|---|
*ISN |
The system variable *ISN contains the unique
ISN number of the record, which itself must be unique.
|
*NUMBER |
The system variable *NUMBER always contains 1
for a valid FIND UNIQUE execution.
|
*COUNTER |
The system variable *COUNTER contains the
number of records found after the evaluation of the WHERE
criterion. *COUNTER is not allowed if the
WHERE clause is missing.
|
Example for FIND UNIQUE
: See the Program FNDUNQ
(reporting mode).
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 of the Database Management System Interfaces documentation.
1
descriptor
[(i)]
|
|
value | OR |
|
value | |||||||||||
THRU
value
[BUT NOT
value
[THRU
value]]
|
||||||||||||||||
2
descriptor [(i)]
|
|
value | ||||||||||||||
3
set-name
|
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
descriptor
|
S | A | A | N | P | I | F | B | D | T | L | no | no | |||||||
value
|
C | S | A | N | P | I | F | B | D | T | L | yes | no | |||||||
set-name
|
C | S | A | no | no |
Syntax Element Description:
See also:
null-indicator |
|
value |
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
null-indicator
|
S | I | no | no | |||||||||||||||
value
|
C | S | N | P | I | F | B | yes | no |
Syntax Element Description:
Syntax Element | Description | |
---|---|---|
null-indicator
|
The null indicator. | |
value |
Possible Values
|
Meaning
|
-1 |
The corresponding field contains no value. | |
0 |
The corresponding field does contain a value. |
basic-search-criteria
can be combined using the
Boolean operators AND, OR, and NOT. Parentheses may also be used to control the order of
evaluation. The order of evaluation is as follows:
( )
: Parentheses
NOT
: Negation (only for
basic-search-criteria
of form [2]).
AND
: AND operation
OR
: OR operation
basic-search-criteria
may be connected by
logical operators to form a complex
search-expression
. The syntax for such a
complex search-expression
is as follows:
[NOT ]
|
basic-search-criteria |
|
search-expression |
See also Example 5 - Various Samples of Complex Search Expression in WITH Clause.
Adabas users may use database fields which are defined as descriptors to construct basic search criteria.
With Adabas, subdescriptors, superdescriptors, hyperdescriptors and phonetic descriptors may be used to construct search criteria.
A subdescriptor is a descriptor formed from a portion of a field.
A superdescriptor is a descriptor whose value is formed from one or more fields or portions of fields.
A hyperdescriptor is a descriptor which is formed using a user-defined algorithm.
A phonetic descriptor is a descriptor which allows the user to perform a phonetic search on a field (for example, a person's name). A phonetic search results in the return of all values which sound similar to the search value.
Which fields may be used as descriptors, subdescriptors, superdescriptors, hyperdescriptors and phonetic descriptors with which file is defined in the corresponding DDM.
Values used with these types of descriptors must be compatible with the internal format of the descriptor. The internal format of a subdescriptor is the same as the format of the field from which the subdescriptor is derived. The internal format of a superdescriptor is binary if all of the fields from which it is derived are defined with numeric format; otherwise, the format is alphanumeric. Phonetic descriptors always have alphanumeric format.
Values for subdescriptors and superdescriptors may be specified in the following ways:
Numeric or hexadecimal constants may be specified. A hexadecimal constant must be used for a value for a superdescriptor which has binary format (see above).
Values in user-defined variable fields may be specified using the
REDEFINE
statement to select the portions that form the
subdescriptor or superdescriptor value.
A descriptor which is contained within a database array may also be used in the construction of basic search criterion. For Adabas databases, such a descriptor may be a multiple-value field or a field contained within a periodic group.
A descriptor contained within a periodic group may be specified with or without an index. If no index is specified, the record will be selected if the value specified is located in any occurrence. If an index is specified, the record is selected only if the value is located in the occurrence specified by the index. The index specified must be a constant. An index range must not be used.
No index must be specified for a descriptor which is a multiple-value field. The record will be selected if the value is located in the record regardless of the position of the value.
See also Example 6 - Various Samples Using Database Arrays.
1
|
descriptor |
|
value | ||||
2
|
descriptor |
|
value
THRU
value
|
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
descriptor
|
S | A | A | N | P | B | no | no | |||||||||||
value
|
C | S | A | N | P | B | yes | no |
Syntax Element Description:
The formats of the descriptor
and the search
value
must be compatible.
This clause only applies to Adabas databases.
This clause is not permitted with Entire System Server.
|
COUPLED
|
[TO ] [FILE ] view-name
|
||||||||
VIA
descriptor1
|
|
descriptor2 | ||||||||
[WITH ]
basic-search-criteria
|
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
descriptor1
|
S | A | A | N | P | B | no | no | |||||||||||
descriptor2
|
S | A | A | N | P | B | no | no |
Note:
Without the VIA
clause, the COUPLED
clause may be specified
up to 4 times; with the VIA
clause, it may be specified up to 42
times.
The COUPLED
clause is used to specify a search which involves the use of
the Adabas coupling facility. This facility permits database descriptors from different
files to be specified in the search criterion of a single FIND
statement.
The same Adabas file must not be used in two different FIND COUPLED
clauses within the same FIND
statement.
A set-name
(see RETAIN Clause)
must not be specified in the
basic-search-criteria
.
Database fields in a file specified within the COUPLED
clause are not
available for subsequent reference in the program unless another FIND
or
READ
statement is issued separately against the coupled file.
Note:
If the COUPLED
clause is used, the main WITH
clause may
be omitted. If the main WITH
clause is omitted, the keywords
AND
/OR
of the COUPLED
clause must not be
specified.
The files used in a COUPLED
clause without VIA
must be
physically coupled using the appropriate Adabas utility (as described in the Adabas
documentation).
See also Example 7 - Using Physically Coupled Files.
The reference to NAME
in the DISPLAY
statement of the above
example is valid since this field is contained in the EMPLOYEES
file,
whereas a reference to MAKE
would be invalid since MAKE
is
contained in the VEHICLES
file, which was specified in the
COUPLED
clause.
In this example, records will be found only if EMPLOYEES
and
VEHICLES
have been physically coupled.
The option VIA descriptor1 =
descriptor2
allows you to logically couple
multiple Adabas files in a search query, where:
The two files need not be physically coupled in Adabas.
See also Example 8 - VIA Clause.
This clause applies only to Adabas and VSAM databases; for VSAM, it is only valid for ESDS.
You can use this clause to specify as operand5
an Adabas ISN (internal sequence number) or VSAM RBA (relative byte
address) respectively, which is to be used as a start value for the selection
of records. operand5
must be in the range from
0
to 4294967295
. For extended ESDS VSAM
files the value of the *ISN
can be up to 16 TB if you specify a
control interval size of 4 KB, or it can be up to 128 TB if you specify a control
interval size of 32 KB.
This clause may be used for repositioning within a FIND
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.
Note:
The start value actually used will not be the value of
operand5
, but the next higher value.
See the program FNDSISN
in the library SYSEXSYN
.
This clause only applies to Adabas and SQL databases.
This clause is not permitted with Entire System Server.
SORTED [BY ]
descriptor
3 [DESCENDING ]
|
The SORTED BY
clause is used to cause Adabas to sort the selected records
based on the sequence of one to three descriptors. The descriptors used for controlling
the sort sequence may be different from those used for selection.
By default, the records are sorted in ascending sequence of values; if you
want them to be in descending sequence, specify the keyword DESCENDING
. The
sort is performed using the Adabas inverted lists and does not result in any records
being read.
Note:
The use of this clause may result in significant overhead if any descriptor used to
control the sort sequence contains a large number of values. This is because the entire
value list may have to be scanned until all selected records have been located in the
list. When a large number of records is to be sorted, you should use the
SORT
statement.
Adabas sort limits (see the ADARUN LS
parameter in the Adabas
documentation) are in effect when the SORTED BY
clause is used.
A descriptor which is contained in a periodic group must not be specified in the
SORTED BY
clause. A multiple-value field (without an index) may be
specified.
Non-descriptors may also be specified in the SORTED BY
clause. However,
this function is not available on mainframes.
If the SORTED BY
clause is used, the RETAIN
clause must not
be used.
See also Example 9 - SORTED BY Clause.
If both the STARTING WITH
and the SORTED BY
clause are used
in the same FIND
statement and the underlying database is Adabas, the
following should be considered.
On Adabas for Mainframes, the FIND
statement is executed in the following
steps:
All records matching the search criterion are gathered and put in ISN sequence.
The records are sorted by the descriptor specified in the SORTED BY
clause.
The record whose ISN value is specified in the STARTING WITH
clause is
positioned in the "sorted-by-descriptor" record list.
The records following the record found under Step 3 are returned in the
FIND
loop.
On Adabas for OpenSystems (Linux and Cloud or Windows) the same statement is executed as follows:
All records matching the search criterion are gathered and put in ISN sequence.
The record whose ISN value is specified in the STARTING WITH
clause is
positioned in the "sorted-by-ISN" record list.
All records following the record found under Step 2 are sorted by the descriptor
specified in the SORTED BY
clause and returned in the FIND
loop.
Example:
If the following program is executed with Adabas for Mainframes and Adabas on Linux and Cloud/Windows:
DEFINE DATA LOCAL 1 V1 VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 CITY 1 #ISN (I4) END-DEFINE FORMAT NL=5 SG=OFF PS=43 AL=15 * PRINT 'FIND' (I) FIND V1 WITH NAME = 'B' THRU 'BALBIN' RETAIN AS 'SET1' IF *COUNTER = 4 THEN #ISN := *ISN END-IF DISPLAY *ISN V1 END-FIND * PRINT / 'FIND .. SORTED BY NAME' (I) FIND V1 WITH 'SET1' SORTED BY NAME DISPLAY *ISN V1 END-FIND * PRINT / 'FIND .. STARTING WITH ISN = ' (I) #ISN (AD=I) FIND V1 WITH 'SET1' STARTING WITH ISN = #ISN DISPLAY *ISN V1 END-FIND * PRINT / 'FIND .. STARTING WITH ISN = ' (I) #ISN (AD=I) ' .. SORTED BY NAME' (I) FIND V1 WITH 'SET1' STARTING WITH ISN = #ISN SORTED BY NAME DISPLAY *ISN V1 END-FIND END
The result is as follows:
- Results on Natural for Mainframes
ISN NAME FIRST-NAME CITY ----- --------------- --------------- --------------- FIND V1 WITH NAME = 'B' THRU 'BALBIN' 12 BAILLET PATRICK LYS LEZ LANNOY 58 BAGAZJA MARJAN MONTHERME 351 BAECKER JOHANNES FRANKFURT 355 BAECKER KARL SINDELFINGEN 370 BACHMANN HANS MUENCHEN 490 BALBIN ENRIQUE BARCELONA 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY FIND .. SORTED BY NAME 370 BACHMANN HANS MUENCHEN 351 BAECKER JOHANNES FRANKFURT 355 BAECKER KARL SINDELFINGEN 58 BAGAZJA MARJAN MONTHERME 12 BAILLET PATRICK LYS LEZ LANNOY 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY 490 BALBIN ENRIQUE BARCELONA FIND .. STARTING WITH ISN = 355 370 BACHMANN HANS MUENCHEN 490 BALBIN ENRIQUE BARCELONA 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY FIND .. STARTING WITH ISN = 355 .. SORTED BY NAME 58 BAGAZJA MARJAN MONTHERME 12 BAILLET PATRICK LYS LEZ LANNOY 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY 490 BALBIN ENRIQUE BARCELONA- Results on Natural for OpenSystems
ISN NAME FIRST-NAME CITY ----- --------------- --------------- --------------- FIND V1 WITH NAME = 'B' THRU 'BALBIN' 12 BAILLET PATRICK LYS LEZ LANNOY 58 BAGAZJA MARJAN MONTHERME 351 BAECKER JOHANNES FRANKFURT 355 BAECKER KARL SINDELFINGEN 370 BACHMANN HANS MUENCHEN 490 BALBIN ENRIQUE BARCELONA 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY FIND .. SORTED BY NAME 370 BACHMANN HANS MUENCHEN 351 BAECKER JOHANNES FRANKFURT 355 BAECKER KARL SINDELFINGEN 58 BAGAZJA MARJAN MONTHERME 12 BAILLET PATRICK LYS LEZ LANNOY 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY 490 BALBIN ENRIQUE BARCELONA FIND .. STARTING WITH ISN = 355 370 BACHMANN HANS MUENCHEN 490 BALBIN ENRIQUE BARCELONA 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY FIND .. STARTING WITH ISN = 355 .. SORTED BY NAME 370 BACHMANN HANS MUENCHEN 650 BAKER SYLVIA OAK BROOK 913 BAKER PAULINE DERBY 490 BALBIN ENRIQUE BARCELONA
A FIND
statement with at most one of these options (SORTED BY
or STARTING WITH ISN
) always returns the same records in the same sequence,
regardless under which system the statement is executed. If, however, both clauses are
used together, the result returned depends on which Adabas platform is used to serve the
database statement.
Therefore, if a Natural program is intended to be used on multiple platforms, the
combination of a SORTED BY
and STARTING WITH ISN
clause in the
same FIND
statement should be avoided.
This clause only applies to Adabas databases.
This clause is not permitted with Entire System Server.
RETAIN AS
operand6
|
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
operand6
|
C | S | A | yes | no |
Syntax Element Description:
See also Example 10 - RETAIN Clause.
There is no specific limit for the number of sets that can be retained or the number of
ISNs in a set. It is recommended that the minimum number of ISN sets needed at one time
be defined. Sets that are no longer needed should be released using the RELEASE
SETS
statement.
If they are not released with a RELEASE
statement, retained sets exist
until the end of the Natural session, or until a logon to another library, when they are
released automatically. A set created by one program may be referenced by another
program for processing or further refinement using additional search criteria.
The records identified by the ISNs in a retained set are not locked against access
and/or update by other users. Before you process records from the set, it is therefore
useful to check whether the original search criteria which were used to create the set
are still valid: This check is done with another FIND
statement, using the
set name in the WITH
clause as a basic search criterion and specifying in a
WHERE
clause the original search criteria (that is, the basic search
criteria as specified in the WITH
clause of the FIND
statement
which was used to create the set).
If the RETAIN
clause is used, the SORTED BY
clause must not
be used.
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 processing limit is specified in a FIND
statement containing a
WHERE
clause, records which are rejected as a result of the
WHERE
clause are not counted against the limit. These records
are, however, counted against a global limit specified in the Natural session parameter
LT
, the GLOBALS
command, or LIMIT
statement.
See also Example 11 - WHERE Clause.
IF NO [RECORDS ] [FOUND ] |
||
END-NOREC |
IF NO [RECORDS ] [FOUND ] |
|||
Syntax Element Description:
Syntax Element | Description |
---|---|
IF NO RECORDS
FOUND |
IF NO RECORDS FOUND Clause:
The If no records meet the specified If this is not desired, specify the statement |
Statement Execution:
If one or more statements are specified with the If no statements are to be executed before entering the loop, the keyword
|
|
END-NOREC |
End of IF NO RECORDS FOUND Clause:
In structured mode, the Natural reserved word In reporting mode, use the |
See also Example 12 - IF NO RECORDS FOUND Clause.
Unless other value assignments are made in the statements accompanying an IF NO
RECORDS FOUND
clause, Natural will reset to empty all database fields which
reference the file specified in the current loop.
Natural system functions are evaluated once for the empty record that is created for
processing as a result of the IF NO RECORDS FOUND
clause.
This clause cannot be used with FIND
FIRST
, FIND
NUMBER
and FIND
UNIQUE
.
See also the example for FIND NUMBER
: program FNDNUM
.
** Example 'FNDPWD': FIND (with PASSWORD clause) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 NAME 2 PERSONNEL-ID * 1 #PASSWORD (A8) END-DEFINE * INPUT 'ENTER PASSWORD FOR EMPLOYEE FILE:' #PASSWORD (AD=N) LIMIT 2 * FIND EMPLOY-VIEW PASSWORD = #PASSWORD WITH NAME = 'SMITH' DISPLAY NOTITLE NAME PERSONNEL-ID END-FIND * END
ENTER PASSWORD FOR EMPLOYEE FILE:
** Example 'FNDCIP': FIND (with PASSWORD/CIPHER clause) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 NAME 2 PERSONNEL-ID * 1 #PASSWORD (A8) 1 #CIPHER (N8) END-DEFINE * LIMIT 2 INPUT 'ENTER PASSWORD FOR EMPLOYEE FILE: ' #PASSWORD (AD=N) / 'ENTER CIPHER KEY FOR EMPLOYEE FILE: ' #CIPHER (AD=N) * FIND EMPLOY-VIEW PASSWORD = #PASSWORD CIPHER = #CIPHER WITH NAME = 'SMITH' DISPLAY NOTITLE NAME PERSONNEL-ID END-FIND * END Output of Program FNDCIP:
ENTER PASSWORD FOR EMPLOYEE FILE: ENTER CIPHER KEY FOR EMPLOYEE FILE:
FIND STAFF WITH NAME = 'SMITH' FIND STAFF WITH CITY NE 'BOSTON' FIND STAFF WITH BIRTH = 610803 FIND STAFF WITH BIRTH = 610803 THRU 610811 FIND STAFF WITH NAME = 'O HARA' OR = 'JONES' OR = 'JACKSON' FIND STAFF WITH PERSONNEL-ID = 100082 THRU 100100 BUT NOT 100087 THRU 100095
When the descriptor used in the basic search criteria is a multiple-value field,
basically four different kinds of results can be obtained (the field
MU-FIELD
in the following examples is assumed to be a multiple-value
field):
FIND XYZ-VIEW WITH MU-FIELD = 'A'
This statement returns records in which at least one occurrence of
MU-FIELD
has the value A
.
FIND XYZ-VIEW WITH MU-FIELD NOT EQUAL 'A'
This statement returns records in which at least one occurrence of
MU-FIELD
does not have the value A
.
FIND XYZ-VIEW WITH NOT MU-FIELD NOT EQUAL 'A'
This statement returns records in which every occurrence of
MU-FIELD
has the value A
.
FIND XYZ-VIEW WITH NOT MU-FIELD = 'A'
This statement returns records in which none of the occurrences of
MU-FIELD
has the value A
.
FIND STAFF WITH BIRTH LT 19770101 AND DEPT = 'DEPT06'
FIND STAFF WITH JOB-TITLE = 'CLERK TYPIST' AND (BIRTH GT 19560101 OR LANG = 'SPANISH')
FIND STAFF WITH JOB-TITLE = 'CLERK TYPIST' AND NOT (BIRTH GT 19560101 OR LANG = 'SPANISH')
FIND STAFF WITH DEPT = 'ABC' THRU 'DEF' AND CITY = 'WASHINGTON' OR = 'LOS ANGELES' AND BIRTH GT 19360101
FIND CARS WITH MAKE = 'VOLKSWAGEN' AND COLOR = 'RED' OR = 'BLUE' OR = 'BLACK'
The following examples assume that the field SALARY
is a descriptor
contained within a periodic group, and the field LANG
is a multiple-value
field.
FIND EMPLOYEES WITH SALARY LT 20000
Results in a search of all occurrences of SALARY
.
FIND EMPLOYEES WITH SALARY (1) LT 20000
Results in a search of the first occurrence only.
FIND EMPLOYEES WITH SALARY (1:4) LT 20000 /* invalid
A range specification must not be specified for a field within a periodic group used as a search criterion.
FIND EMPLOYEES WITH LANG = 'FRENCH'
Results in a search of all values of LANG
.
FIND EMPLOYEES WITH LANG (1) = 'FRENCH' /* invalid
An index must not be specified for a multiple-value field used as a search criterion.
** Example 'FNDCPL': FIND (using coupled files) ** NOTE: Adabas files must be physically coupled when using the ** COUPLED clause without the VIA clause. ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 NAME 1 VEHIC-VIEW VIEW OF VEHICLES 2 MAKE END-DEFINE * FIND EMPLOY-VIEW WITH CITY = 'FRANKFURT' AND COUPLED TO VEHIC-VIEW WITH MAKE = 'VW' DISPLAY NOTITLE NAME END-FIND * END
** Example 'FNDVIA': FIND (with VIA clause) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 2 FIRST-NAME 1 VEHIC-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID END-DEFINE * FIND EMPLOY-VIEW WITH NAME = 'ADKINSON' AND COUPLED TO VEHIC-VIEW VIA PERSONNEL-ID = PERSONNEL-ID WITH MAKE = 'VOLVO' DISPLAY PERSONNEL-ID NAME FIRST-NAME END-FIND * END
Page 1 05-01-17 13:18:22 PERSONNEL NAME FIRST-NAME ID --------- -------------------- -------------------- 20011000 ADKINSON BOB
** Example 'FNDSOR': FIND (with SORTED BY clause) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 CITY 2 NAME 2 FIRST-NAME 2 PERSONNEL-ID END-DEFINE * LIMIT 10 FIND EMPLOY-VIEW WITH CITY = 'FRANKFURT' SORTED BY NAME PERSONNEL-ID DISPLAY NOTITLE NAME (IS=ON) FIRST-NAME PERSONNEL-ID END-FIND * END
NAME FIRST-NAME PERSONNEL ID -------------------- -------------------- --------- BAECKER JOHANNES 11500345 BECKER HERMANN 11100311 BERGMANN HANS 11100301 BLAU SARAH 11100305 BLOEMER JOHANNES 11200312 DIEDRICHS HUBERT 11600301 DOLLINGER MARGA 11500322 FALTER CLAUDIA 11300311 HEIDE 11400311 FREI REINHILD 11500301
** Example 'RELEX1': FIND (with RETAIN clause and RELEASE statement) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 CITY 2 BIRTH 2 NAME * 1 #BIRTH (D) END-DEFINE * MOVE EDITED '19400101' TO #BIRTH (EM=YYYYMMDD) * FIND NUMBER EMPLOY-VIEW WITH BIRTH GT #BIRTH RETAIN AS 'AGESET1' IF *NUMBER = 0 STOP END-IF * FIND EMPLOY-VIEW WITH 'AGESET1' AND CITY = 'NEW YORK' DISPLAY NOTITLE NAME CITY BIRTH (EM=YYYY-MM-DD) END-FIND * RELEASE SET 'AGESET1' * END
NAME CITY DATE OF BIRTH -------------------- -------------------- ---------- RUBIN NEW YORK 1945-10-27 WALLACE NEW YORK 1945-08-04
** Example 'FNDWHE': FIND (with WHERE clause) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 2 JOB-TITLE 2 CITY END-DEFINE * FIND EMPLOY-VIEW WITH CITY = 'PARIS' WHERE JOB-TITLE = 'INGENIEUR COMMERCIAL' DISPLAY NOTITLE CITY JOB-TITLE PERSONNEL-ID NAME END-FIND * END
CITY CURRENT PERSONNEL NAME POSITION ID -------------------- ------------------------- --------- -------------------- PARIS INGENIEUR COMMERCIAL 50007300 CAHN PARIS INGENIEUR COMMERCIAL 50006500 MAZUY PARIS INGENIEUR COMMERCIAL 50004700 FAURIE PARIS INGENIEUR COMMERCIAL 50004400 VALLY PARIS INGENIEUR COMMERCIAL 50002800 BRETON PARIS INGENIEUR COMMERCIAL 50001000 GIGLEUX PARIS INGENIEUR COMMERCIAL 50000400 KORAB-BRZOZOWSKI
** Example 'FNDIFN': FIND (using IF NO RECORDS FOUND) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 2 FIRST-NAME 1 VEHIC-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID 2 MAKE END-DEFINE * LIMIT 15 EMP. READ EMPLOY-VIEW BY NAME STARTING FROM 'JONES' /* VEH. FIND VEHIC-VIEW WITH PERSONNEL-ID = PERSONNEL-ID (EMP.) IF NO RECORDS FOUND MOVE '*** NO CAR ***' TO MAKE END-NOREC /* DISPLAY NOTITLE NAME (EMP.) (IS=ON) FIRST-NAME (EMP.) (IS=ON) MAKE (VEH.) END-FIND /* END-READ END
NAME FIRST-NAME MAKE -------------------- -------------------- -------------------- JONES VIRGINIA CHRYSLER MARSHA CHRYSLER CHRYSLER ROBERT GENERAL MOTORS LILLY FORD MG EDWARD GENERAL MOTORS MARTHA GENERAL MOTORS LAUREL GENERAL MOTORS KEVIN DATSUN GREGORY FORD JOPER MANFRED *** NO CAR *** JOUSSELIN DANIEL RENAULT JUBE GABRIEL *** NO CAR *** JUNG ERNST *** NO CAR *** JUNKIN JEREMY *** NO CAR *** KAISER REINER *** NO CAR ***
** Example 'FNDVAR': FIND (using *ISN, *NUMBER, *COUNTER) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 2 CITY END-DEFINE * LIMIT 3 FIND EMPLOY-VIEW WITH CITY = 'MADRID' DISPLAY NOTITLE PERSONNEL-ID NAME *ISN *NUMBER *COUNTER END-FIND * END
PERSONNEL NAME ISN NMBR CNT ID --------- -------------------- ----------- ----------- ----------- 60000114 DE JUAN 400 41 1 60000136 DE LA MADRID 401 41 2 60000209 PINERO 405 41 3
In the following example, first all people named SMITH are selected from the
EMPLOYEES
file. Then the PERSONNEL-ID
from the
EMPLOYEES
file is used as the search key for an access to the
VEHICLES
file.
** Example 'FNDMUL': FIND (with multiple files) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 2 FIRST-NAME 1 VEHIC-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID 2 MAKE END-DEFINE * LIMIT 15 EMP. FIND EMPLOY-VIEW WITH NAME = 'SMITH' /* VEH. FIND VEHIC-VIEW WITH PERSONNEL-ID = EMP.PERSONNEL-ID IF NO RECORDS FOUND MOVE '*** NO CAR ***' TO MAKE END-NOREC DISPLAY NOTITLE EMP.NAME (IS=ON) EMP.FIRST-NAME (IS=ON) VEH.MAKE END-FIND END-FIND END
The resulting report shows the NAME
and FIRST-NAME
(obtained
from the EMPLOYEES
file) of all people named SMITH as well as the
MAKE
of each car (obtained from the VEHICLES
file) owned by
these people.
NAME FIRST-NAME MAKE -------------------- -------------------- -------------------- SMITH GERHARD ROVER SEYMOUR *** NO CAR *** MATILDA FORD ANN *** NO CAR *** TONI TOYOTA MARTIN *** NO CAR *** THOMAS FORD SUNNY *** NO CAR *** MARK FORD LOUISE CHRYSLER MAXWELL MERCEDES-BENZ MERCEDES-BENZ ELSA CHRYSLER CHARLY CHRYSLER LEE *** NO CAR *** FRANK FORD
FIND 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-FIND
FIND 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-FIND