Use select/omit fields to tell the OS/400 program how to select or omit records when your program retrieves them using this record format. The only records affected are those from the physical file(s) specified for the PFILE or JFILE keyword for this record format.
The following rules apply to select/omit fields in logical files:
If the field name is specified more than once, the first occurrence is used.
The field name on a CONCAT or RENAME keyword and the associated field name in positions 19 through 28 cannot both be specified as select/omit fields.
The parameter name on the SST keyword is not valid as a select/omit field unless it is defined elsewhere in the logical file record format.
For join logical files, the select/omit field name you specify must be specified at the field level in positions 19 through 28.
When using the select/omit fields, specify either S or O in position 17. By specifying either S or O, the select and omit comparison statements are ORed together. The system treats the ORed select and omit comparison statements independently from one another. That is, if the select or omit comparison condition is met, the record is either selected or omitted. If the condition is not met, the system proceeds to the next comparison.
By specifying a blank in position 17, the select and omit comparison statements are ANDed together. The combined comparisons must be met before the record is selected or omitted. See Figure 20 and Figure 21. In positions 19 through 28, specify a field name whose contents at processing time determine whether the record is to be selected or omitted based on the select/omit keyword specified for this field. The select/omit keywords are COMP, RANGE, and VALUES. The last select/omit specification can be made with the ALL keyword, but a field name is not permitted.
The field must appear in both the physical file record format and the logical file record format. Select/omit statements must follow all field and key field level entries for the record format. You can specify both select and omit for the same record format. The following information applies:
There are limits to the number of select/omit statements you can specify in a single logical file. If you specify many select/omit statements and you cannot create the file, reduce the overhead for the file through the following changes in the specifications, in decreasing order of importance:
You cannot specify a floating-point field as a select/omit field.
It is possible to have an access path with select/omit and process the file in arrival sequence. For example, CPYF can be specified with FROMRCD(1) or the high-level language may not request keyed processing. In this case, the processing is the same as if the DYNSLT keyword had been specified.
Figure 20 shows how to specify the select/omit field using ANDed select statements.
Figure 20. Specifying the Select/Omit Field (Example 1)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PARTS) 00020A PNO 00030A DSC 00040A UPR 00050A QOH 00060A K PNO 00070A S UPR COMP(GT 5.00) 00080A QOH COMP(LT 10) 00090A O ALL A
In Figure 20, records are selected only if they satisfy two select statements: the first statement selects records in which the value of field UPR is greater than 5.00, and the second statement selects records in which the value of field QOH is less than 10. S is not specified in position 17 for field QOH. Therefore, these select statements are ANDed together. For a record to be read by a program, both conditions specified must be true.
Figure 21 shows how to specify the select/omit field using an omit statement ORed with two select statements ANDed together.
Figure 21. Specifying the Select/Omit Field (Example 2)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PARTS) 00020A PNO 00030A DSC 00040A UPR 00050A QOH 00060A K PNO 00070A O DSC COMP(EQ 'HAMMER') 00080A S UPR COMP(GT 5.00) 00090A QOH COMP(LT 10) 00100A O ALL A
In Figure 21, records are supplied to the program if they pass both of the following tests:
Figure 22 shows several ways to specify the same select/omit logic.
Figure 22. Specifying the Select/Omit Field (Example 3)
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A S ST COMP(EQ 'NY') 00020A REP COMP(EQ 'JSMITH') (1) 00030A YEAR COMP(LT 78) 00040A O ALL A 00050A O YEAR COMP(GE 78) 00060A S ST COMP(EQ 'NY') (2) 00070A REP COMP(EQ 'JSMITH') 00080A O ALL A 00090A O REP COMP(NE 'JSMITH') 00100A O ST COMP(NE 'NY') (3) 00110A S YEAR COMP(LT 78) 00120A O ALL A
In Figure 22, you want to select all the records before 1978 for a sales representative named JSMITH in the state of New York. There are three ways to code this example.