The search-criteria in Detail
The search-criteria is mainly used without the softcoupling element. The following are detailed examples of the search-criteria.
Simple search criterion
@ADADCL5, EMPLOYEES PERSONNEL-ID.
The value buffer definition is:
01 VALUE-BUF5.
02 V-PERSONNEL-ID5 PIC X(8) VALUE ’ ’.
Using a value operator
A value operator may be used to indicate the logical operation to be performed between the preceding descriptor and its corresponding value in the value buffer. In this case, the name of the descriptor in the value buffer remains the same. The search buffer contains the value operator (not relevant to the Adabas Pre-Compiler programmer).
The following example is used to find all personnel whose salary is less than the value specified in the value buffer.
@ADADCL5, EMPLOYEES SALARY LT
Using a relational null
If the descriptor is defined in the database as a relational null field, Adabas allows you to select all records where the descriptor value is null (contains no value) or to select all records where the descriptor value is not null (no matter what the value is).
The Adabas Pre-Compiler user may specify in the search criteria the keyword NULL or (NOT-NULL) within parentheses as a suffix to the descriptor name. In this case, Adabas Pre-Compiler takes care of the correct value buffer assignments for this field and the user should not assign any value to this field within the value buffer.
In the Predict definition, this field should contain R or U in the field suppression attribute.
The following search criteria selects all records from the file TEST1 on which the NAME is the name specified in the value buffer and the descriptor COLOR does not contain a value. Note that the descriptor COLOR must be defined with the relational null support.
@ADADCL7, TEST1 NAME AND COLOR(NULL).
The value buffer definition is:
01 VALUE-BUF7.
02 V-NAME7 PIC X(20) VALUE ’ ’.
02 V-COLOR7 PIC S9(4) COMP VALUE -1.
The following search criteria selects all records from the file TEST1 on which the NAME is the name specified in the value buffer and the descriptor COLOR contains any value. Note that the descriptor COLOR should be defined with the relational null support.
@ADADCL8, TEST1 NAME AND COLOR(NOT-NULL).
The value buffer definition is:
01 VALUE-BUF8.
02 V-NAME8 PIC X(20) VALUE ’ ’.
02 V-COLOR8 PIC S9(4) COMP VALUE 0.
Using a specific occurrence
If the descriptor is a field within a periodic-group, you may request special occurrences of the descriptor by adding the occurrence number within parentheses as a suffix to the descriptor name.
In addition to the regular suffixes, the descriptor in the value buffer is suffixed by - and the occurrence number.
In the following example, the search-criteria specifies selection, based on the third occurrence of the field SALARY that is in the periodic-group INCOME. If no occurrence number was present, the search would be for the value in all the occurrences of the periodic group and there would be no suffix to the field-name in the value buffer.
@ADADCL6, EMPLOYEES SALARY(3).
The value buffer definition is:
01 VALUE-BUF6.
02 V-SALARY6-03 PIC 9(9) COMP-3 VALUE 0.
Using a command-id
The search-criterion may also be a command-id specifying an ISN list that is a result of another @FIND command. In that case, you specify the (command-id), without including any value in the value buffer for that search-criterion. For example:
@ADADCL1, EMPLOYEES (XY01).
Operators
The
AND operator connects two search expressions with different descriptors using a logical AND operation. Several
AND operators may occur in a
search-criteria. For example:
@ADADCL8, EMPLOYEES CITY AND SALARY LT.
The value buffer definition is:
01 VALUE-BUF8.
02 V-CITY8 PIC X(20) VALUE ’ ’.
02 V-SALARY8 PIC 9(9) COMP-3 VALUE 0.
In the case of a multiple value field that is a descriptor, the AND operator may combine two expressions with the same multiple value field. For example:
@ADADCL7, EMPLOYEES LANG AND LANG.
The value buffer definition is:
01 VALUE-BUF7.
02 V-LANG7 PIC X(3) VALUE ’ ’.
02 V-LANG7-01 PIC X(3) VALUE ’ ’.
The additional suffix -01 distinguishes between the two fields in the value buffer. This suffix is actually a counter and is increased by one for every additional appearance of the field LANG in the search-criteria.
Note that when the AND operator is to operate between different occurrences of a descriptor within a periodic-group, the suffix represents the occurrences. In this case, the occurrence is written in parentheses and Adabas Pre-Compiler uses a two-digit number suffixed to the descriptor name in the value buffer. For example:
@ADADCL13, EMPLOYEES SALARY(1) AND
SALARY(3) AND SALARY(5).
The value buffer definition is:
01 VALUE-BUF13.
02 V-SALARY13-01 PIC 9(9) COMP-3 VALUE 0.
02 V-SALARY13-03 PIC 9(9) COMP-3 VALUE 0.
02 V-SALARY13-05 PIC 9(9) COMP-3 VALUE 0.
The
COUPLED operator specifies that the succeeding
search-criteria contain descriptors from another file that is coupled to the main file specified by the
@ADADCL command. The logical operation between the preceding
search-criteria (of the main file) and the succeeding one is
AND. If no physical coupling exists the
SOFTCOUPLED clause also appears at the beginning of the
search-criteria. For example:
@ADADCL9, EMPLOYEES NAME COUPLED VEHICLES
MAKE.
The value buffer definition is:
01 VALUE-BUF9.
02 V-NAME9 PIC X(20) VALUE ’ ’.
02 V-MAKE9 PIC X(20) VALUE ’ ’.
Note:
It is possible to start the search-criteria with a COUPLED clause, immediately after the SOFTCOUPLED clause if exists, which means that there are no selection criteria for the main file, but only for the coupled file.
The
THRU operator signifies that a range of values is to be searched for the descriptor specified before and after the
THRU keyword. In this case, the value buffer contains two fields for the same descriptor. For the low value of the range, a field with an additional
F suffix is declared and for the high value, a second field with the additional
T suffix (indicating a range From - To). For example:
@ADADCL10, EMPLOYEES PERSONNEL-ID THRU
PERSONNEL-ID.
The value buffer definition is:
01 VALUE-BUF10.
02 V-PERSONNEL-ID10-F PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID10-T PIC X(8) VALUE ’ ’.
The
BUT-NOT clause excludes a specified value or a range of values form the previous range. If the
BUT-NOT clause is used, another field of the descriptor is declared in the value buffer (or two fields in case of a range). The field has an additional
N suffix. In case of a range, the suffix is
FN (From) and
TN (To) for the low and high values. For example:
@ADADCL11, EMPLOYEES PERSONNEL-ID THRU
PERSONNEL-ID BUT-NOT PERSONNEL-ID.
The value buffer definition is:
01 VALUE-BUF11.
02 V-PERSONNEL-ID11-F PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID11-T PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID11-N PIC X(8) VALUE ’ ’.
The
OR operator specifies that the results of two search expressions are to be combined using a logical
OR operation. Several
OR operators may occur in a
search-criteria. The
OR operator can be used between different
search-criteria which include different descriptors or can be used when it is required to select records based on several values of the same descriptor in one
@FIND command. If the
OR operator operates on the same descriptor, Adabas Pre-Compiler uses a two digit counter, which is additionally suffixed to the descriptor name in the value buffer. For example:
@ADADCL13, EMPLOYEES NAME OR NAME OR NAME OR
NAME.
The value buffer definition is:
01 VALUE-BUF13.
02 V-NAME13-01 PIC X(20) VALUE ’ ’.
02 V-NAME13-02 PIC X(20) VALUE ’ ’.
02 V-NAME13-03 PIC X(20) VALUE ’ ’.
02 V-NAME13-04 PIC X(20) VALUE ’ ’.
The following example illustrates the use of the OR operator between ranges:
@ADADCL14, EMPLOYEES PERSONNEL-ID THRU
PERSONNEL-ID OR PERSONNEL-ID THRU
PERSONNEL-ID OR PERSONNEL-ID THRU
PERSONNEL-ID.
The value buffer definition is:
The VALUE-BUF14.
02 V-PERSONNEL-ID14-01F PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID14-01T PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID14-02F PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID14-02T PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID14-03F PIC X(8) VALUE ’ ’.
02 V-PERSONNEL-ID14-03T PIC X(8) VALUE ’ ’.