DDS Select/Omit Field Name

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:

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.

(1)
All records must be compared with the select fields ST, REP, and YEAR before they can be selected or omitted.

(2)
All records in and after 1978 are omitted in the first comparison. Then, only the records before 1978 are compared with ST and REP. Only two select fields must be satisfied. This way is more efficient than method (1).

(3)
All records that are not associated with JSMITH in the state of New York are omitted in the first and second comparisons. Then, all records left are compared to YEAR. This is more efficient than method (1) or method(2).


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]