DDS for physical and logical files

Name for physical and logical files (positions 19 through 28)

Use these positions to specify names of the following:

Note:
The file name is specified through the Create Physical File (CRTPF) command, not in the DDS.

Refer to the DDS Concepts information for rules to use when specifying record or field names in DDS.

Names must begin in position 19.

You must specify the name type in position 17, unless you are specifying a field name or select/omit AND condition.

Figure 3 shows how to code names for a physical file. Figure 1 and Figure 2 show how to code names for logical files.

Record format

When you specify R in position 17, the name specified in positions 19 through 28 is a record format name.

Record format for physical files:

Only one record format name is allowed for a physical file. Specify the record format name in one of two ways:

Record format for simple and multiple format logical files:

You can specify more than one record format name. However, each name must be unique within the file. See the appropriate high-level language manual for exceptions.

Specify the record format name in one of three ways:

The record format name can be the same as the file name specified in the create file command. However, a warning message is sent if the names are not unique. Some high-level language processors, such as RPG, do not allow record format and file names to be the same.

Use the PFILE keyword in conjunction with the record format name to specify the physical files with which the record format is to be associated. A record format can have more than one physical file specified on the PFILE keyword. If no fields are defined and the FORMAT keyword is not specified, the format of the first file specified in the PFILE keyword is used as the format for all the physical files. (This format is used for field attribute references and attribute and name checking.)

Join logical files:

Only one record format name can be specified. Specify the record format name as the name of a new record format with field names specified in this logical file. Every field in the record format for a join logical file must be identified by the name in positions 19 through 28. Physical file fields that are parameters of the RENAME, CONCAT, and SST keywords are part of the logical file record format only if you specify the field names elsewhere in the record format.

The JFILE keyword is required at the record level. It specifies the physical files that the record format joins.

Field name

When position 17 is left blank, the name specified in positions 19 through 28 is a field name. You cannot specify field names if you specify the FORMAT keyword.

Physical files require that each field be named. These names must be unique within the record format. The field names appear in the physical buffer in the same order that they are specified in the DDS.

If you are describing a simple or multiple format logical file, you can use the record format as it exists in the physical file on which this logical file is based, and you do not have to specify field names.

If you do not use the record format as it exists in the physical file, you must name each field specified in a logical file. In a simple or multiple format logical file, each field name must be unique within the record format and must correspond to a field in the physical file record format. The field name order is the order in which the fields appear to programs using the logical file.

The name you give to a field in a logical file record format is usually the same as the corresponding field name in the physical file record format. If different, the two names must be equated by using the RENAME keyword. A field in a logical file record format can also represent the concatenation of two or more fields from the physical file (see CONCAT (Concatenate) keyword--logical files only). The SST keyword can also be used to describe a substring of a field from the physical file in the logical file format.

Note:
The sequence in which the field names are specified in the logical file is important. If the same physical field is specified more than once in a record format in the logical file (by using either RENAME or CONCAT), the sequence in which the fields are specified in the logical file is the sequence that the data is moved to the physical file. Thus, the value of the field the last time the field is specified in the logical file is the value in the physical record.

Key field name

When you specify K in position 17, the name specified in positions 19 through 28 is a key field name. It must be one of the field names within the physical file record format. The contents of this field are used to sequence the records for retrieval from the database. Specifying a key is optional. If no key field is specified, the default sequence is arrival sequence (the order that the records were put into the file).

Use key fields (and optionally, select/omit fields) to define a keyed sequence access path for record formats in the logical file member. The logical file member includes the physical file members specified on the DTAMBRS parameter on the Create Logical File (CRTLF) or Add Logical File Member (ADDLFM) commands.

You can change the sequence of records as they are read from the file by specifying a sequencing keyword. The sequencing keywords are ALTSEQ, NOALTSEQ, SIGNED, UNSIGNED, ABSVAL, ZONE, DIGIT, DESCEND, FCFO, FIFO, and LIFO. Refer to the discussion of each of these keywords for more information.

When you do not specify any sequencing keywords for a key field, the default sequence for that key field is ascending order. The default for character, hexadecimal, date, time, and timestamp fields is the UNSIGNED attribute. The default for numeric fields is the SIGNED attribute, except for zoned decimal fields (S specified in position 35) in the following cases:

If you specify more than one record format for a logical file or more than one physical file for the PFILE keyword, you must specify at least one key field for all record formats of that logical file.

A key can have more than one key field. This is called a composite key. In a composite key, specify the key field names in the order of importance (major to minor), and specify each key field name on a separate line.

Figure 4 shows a multiple format logical file with two record formats, one of which uses a composite key. In this example, RECORD1 has a single key field, FIELD1. RECORD2 has a composite key that includes FIELD4 and FIELD5.

Figure 4. Specifying a Multiple Format Logical File with Two Record Formats

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R RECORD1                   PFILE(PF1)
00020A            FIELD1
00030A            FIELD2
00040A            FIELD3
00050A          K FIELD1
00060A*
00070A          R RECORD2                   PFILE(PF2)
00080A            FIELD4
00090A            FIELD5
00100A          K FIELD4
00110A          K FIELD5
     A

If you do not specify a key field for a logical file, the file you are defining has an arrival sequence access path.

The number of fields that make up a key is restricted to 120. The total key length cannot exceed 2000 bytes. (If the FCFO keyword is specified, the total key length cannot exceed 1995 bytes.) The total key length includes the length of each key field. If any of the key fields allow the null value, add 1 byte for each key field that allows the null value. The OS/400 program uses the extra byte to determine whether the key contains the null value. If any of the key fields is variable length, add 2 bytes for each variable-length key field. The OS/400 program uses the extra 2 bytes to store the allocated length of the field.

When you specify more than one record format in a logical file, an additional byte for the first *NONE key field position is required. An additional byte also may be required for each additional key field position. The OS/400 program uses the extra bytes when records from different physical files have duplicate key values.

For example, suppose a key consists of fields named FIELDA, FIELDB, and FIELDC (in that order). The DDS appears as shown in Figure 5.

Figure 5. Composite Key

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A* SAMPLE COMPOSITE KEY (PHYSICAL FILE)
00020A          R RECORD
00030A            FIELDA         3  0
00040A            FIELDB         3  0
00050A            FIELDC         3  0
00060A            FIELDD         3  0
00070A          K FIELDA
00080A          K FIELDB
00090A          K FIELDC
     A
 
Note:
Lines 00070 to 00090 make up the composite key.

The records are sequenced in the following order:

Consider the following file:

Record FIELDA FIELDB FIELDC
1 333 99 67
2 444 10 45
3 222 34 23
4 222 12 01
5 222 23 45
6 111 06 89
7 222 23 67

Assuming ascending sequencing for all fields, the records are retrieved in this order:

Record FIELDA FIELDB FIELDC
6 111 06 89
4 222 12 01
5 222 23 45
7 222 23 67
3 222 34 23
1 333 99 67
2 444 10 45

The following information applies:

See SIGNED (Signed) keyword for physical and logical files for an example that includes a key field with negative (-) contents.

Special restrictions apply to key field specifications when either FILETYPE(*SRC) is used on the Create Physical File (CRTPF) command or for the Create Source Physical File (CRTSRCPF) command. .

For logical files, the following rules apply to fields that you specify as key fields:

If you are concatenating numeric with either character or hexadecimal, you cannot specify the numeric fields as key fields. If you are concatenating zoned decimal and fields of any other numeric data type, you cannot specify the fields of the other data types as key fields.

Figure 6 illustrates which concatenated fields can and cannot be used as key fields.

Figure 6. Correct and Incorrect Concatenated Fields

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R RECORD1                   PFILE(PF1)
00020A            FLD1
00030A            FLD2
00040A            Z                         CONCAT(ZFLD PFLD)
00050A            A                         CONCAT(AFLD NFLD)
00060A          K ZFLD
00070A          K AFLD
     A

In physical file PF1, ZFLD is zoned decimal and PFLD is packed decimal. Therefore Z is zoned decimal, and PFLD cannot be used as a key field. ZFLD and Z can be used as key fields but not in the same record format.

In physical file PF1, AFLD is a character field and NFLD is a numeric field. Therefore A is character, and NFLD cannot be used as a key field. AFLD and A can be used as key fields but not in the same record format.

DDS Access Path Keywords

You can specify one or more access path keywords to affect the way the OS/400 program builds and uses key values. The access path keywords are:

File Level Key Field Level
ALTSEQ
FCFO
FIFO
LIFO
REFACCPTH
UNIQUE
DESCEND
DIGIT
SIGNED
UNSIGNED
ZONE

Different key fields within a composite key can have different access path keywords.

DDS Logical Files with More than One Record Format

When you specify more than one record format in a logical file, you must specify at least one key field for every record format in the logical file. It is not necessary to specify the same number of key fields in each key. Also, key fields specified in one record format must have the same field attributes and access path keywords as the corresponding key fields in other record formats in the same logical file. For variable-length key fields, a variable-length key field will not be allowed to align with a fixed-length key field, even if the field types and lengths are the same.

A key is required for every record format so that the logical file members can have a single access path sequencing records of each record format. When records are returned from the various members of the physical file on which the logical file is based, they are merged according to the values of the key fields in the access path for the logical file member.

When records of a logical file member are sequenced, the OS/400 program builds a key value for each record by concatenating the values in its key fields. The key value is then used to build the access path for use by your program.

Each key field in a composite key has a key position. The first key field specified is in position 1, the second key field specified is in position 2, and so on. During I/O operations to a logical file, the OS/400 program compares the key values of the records written to or read from the database. When you create a logical file that has more than one record format (with or without different key fields specified), the OS/400 program performs key position attribute checking. For key position attribute checking to succeed, key fields of different record formats that are in the same key positions must have the same data type, length, decimal positions, and access path keywords specified at the key field level. This ensures a meaningful record sequence from the comparisons made during an I/O operation.

Floating-point fields used as key fields must have the same data type and precision but need not have the same length and decimal positions.

In Figure 7, FIELD1, FLD1, and F1 must have the same attributes, and FIELD2, FLD2, and F2 must have the same attributes. FIELD1, FLD1, and F1 are in key position 1; FIELD2, FLD2, and F2 are in key position 2. One record format can have more key fields than another, and the additional fields do not need key position attribute checking. FLD3 is such a field.

Figure 7. Key Field Attribute Checking

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R RECORD1                   PFILE(PF1)
00020A            FIELD1         5  0
00030A            FIELD2        10
00040A            FIELD3        10
00050A          K FIELD1
00060A          K FIELD2                    DESCEND
00070A*
00080A          R RECORD2                   PFILE(PF2)
00090A            FLD1           5  0
00100A            FLD2          10
00110A            FLD3          20
00120A          K FLD1
00130A          K FLD2                      DESCEND
00140A          K FLD3
     A*
     A          R RECORD3                   PFILE(PF3)
     A            F1             5  0
     A            F2            10
     A            F3            30
     A          K F1
     A          K F2                        DESCEND
     A
     A

For examples of key fields in a logical file with more than one record format, refer to Figure 1. In Figure 1, fields named ITEM are specified in each key. For record formats INVFMT and ACTFMT, ITEM is the only key field specified. For record format ORDFMT, a composite key is specified. This composite key includes ITEM, SHPYR, SHPMO, and SHPDA. Each of the fields used in a key must also exist at the field level. Therefore, ITEM must exist in the record format for the physical file INVENTORY so that it can be copied into this logical file for INVFMT. Also, ITEM must exist in the record format for the logical file ACCOUNTL so that it can be copied into this logical file for ACTFMT. ITEM must also exist in physical file ACCOUNTS.

Using *NONE in the Key Field When Creating a DDS File

Two conditions occur in which key fields having the same key position should not be compared. The two conditions are:

To avoid unwanted comparisons between key fields, specify *NONE in place of one of them and move the displaced key field to the next key position. The OS/400 program compares the values of key positions before and after *NONE, but retrieves the affected records in the order in which the record formats are specified in the DDS for the logical file.

You can specify *NONE two or more times on the following lines to displace a key field to a key position for which a comparison of key field attributes is relevant to your application.

Figure 8 shows *NONE as the key field.

Figure 8. Specifying *NONE as the Key Field

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          R RECORD1                   PFILE(PF1)
     A            FIELD1         6A
     A            FIELD2         4A
     A            FIELD3        10A
     A          K FIELD1
     A          K FIELD2
     A          K FIELD3
     A
     A          R RECORD2                   PFILE(PF2)
     A            FLD1           3A
     A            FLD2           4A
     A            FLD3          12A
     A          K *NONE
     A          K FLD2
     A
     A          R RECORD3                   PFILE(PF3)
     A            F1             6A
     A            F2             4A
     A            F3            10A
     A          K F1
     A          K F2
     A          K F3
     A

In Figure 8, the attributes for FIELD2, FLD2, and F2, must be identical. Since you specified *NONE for the first key field of the second record, then FIELD1 and F1 (first key field of the first record and first key field of the third record) must have identical attributes. FIELD3 and F3 must also have identical attributes; there is no corresponding field in the second record format.

Figure 9 through Figure 12 field.

Figure 9. Specifying the Key Field (Example 1)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R CLSHST                    PFILE(CLSHSTP)
00020A          K EMPNBR  (1)
00030A          K CLSDTE  (2)
00040A*
00050A          R JOBHST                    PFILE(JOBHSTP)
00060A          K EMPNBR  (1)
00070A          K JOBDTE  (2)
     A

Record Format Key Positions

(1) (2)
CLSHST EMPNBR CLSDTE
JOBHST EMPNBR JOBDTE

DDS Example 1

In Figure 9, a logical file views records of two physical files through two different record formats: CLSHST (class history) and JOBHST (job history). In the logical file, the records from the two physical files can be merged together and sequenced by employee identification number (EMPNBR) by specifying EMPNBR in key position 1.

All records that have the same key value for EMPNBR pertain to the same employee. To merge and sequence all records for a given employee into a single history of classes and job assignments, specify CLSDTE (date of class) and JOBDTE (date of job assignment) in key position 2 for the two record formats, as shown in Figure 9.

Suppose that the job assignment dates and class dates are the dates (month/year) that the class or assignment started. Records for three students are retrieved in the following order:

EMPNBR CLSDTE JOBDTE Description
1005 3/79
Completed class
1005 4/79
Left to begin new job
1005
4/79 Completed job
1005 6/79
Completed class
1006
1/79 Completed job
1006
2/79 Completed job
1006 3/79
Completed class
1006 5/79
Transferred to new location
1007
1/79 Completed job
1007
4/79 Completed job
1007
7/79 Completed job
1007 8/79
Left because of illness

The above report provides a continuous history for each student.

DDS Example 2

In Figure 10, another logical file views the same two physical files as in Example 1, but the second record format in the logical file has *NONE specified in key position 2.

Figure 10. Specifying the Key Field (Example 2)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R CLSHST                    PFILE(CLSHSTP)
00020A          K EMPNBR  (1)
00030A          K CLSDTE  (2)
00040A*
00050A*
00060A          R JOBHST                    PFILE(JOBHSTP)
00070A          K EMPNBR  (1)
00080A          K *NONE   (2)
00090A          K JOBDTE  (3)
     A

Record Format Key Positions


(1) (2) (3)
CLSHST EMPNBR CLSDTE *NONE
JOBHST EMPNBR *NONE JOBDTE

As in Figure 9, all records from the two physical files are first merged and sequenced together on employee number (EMPNBR). However, the records for each student are merged and sequenced first on class date (CLSDTE) and then on job assignment date (JOBDTE). The set of records used for Figure 9 are now retrieved as follows:

EMPNBR CLSDTE JOBDTE Description
1005 3/79
Completed class
1005 4/79
Left to begin new job
1005 6/79
Completed class
1005
4/79 Completed job
1006 3/79
Completed class
1006 5/79
Transferred to new location
1006
1/79 Completed job
1006
2/79 Completed job
1007 8/79
Left because of illness
1007
1/79 Completed job
1007
4/79 Completed job
1007
7/79 Completed job

When several adjacent record formats have *NONE in the same key position, they form a set, relative to record formats specified before and after them, that functions in sequencing as an individual record format. Key fields specified after *NONE serve to merge and sequence records of the formats within the set. The following example shows how several record formats function as a set.

DDS Example 3

In Figure 11, consider a logical employee file over five physical files.

Figure 11. Specifying the Key Field (Example 3)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R EMPMST                    PFILE(EMPMSTP)
00020A          K EMPNBR  (1)
00030A*
00040A          R CLSREG                    PFILE(CLSREGP)
00050A          K EMPNBR  (1)
00060A          K CLSDTE  (2)
00070A*
00080A          R CLSHST                    PFILE(CLSHSTP)
00090A          K EMPNBR  (1)
00100A          K CLSDTE  (2)
00110A*
00120A          R JOBHST                    PFILE(JOBHSTP)
00130A          K EMPNBR  (1)
00140A          K *NONE   (2)
00150A          K JOBDTE  (3)
00160A*
00170A          R ACTHST                    PFILE(ACTHSTP)
00180A          K EMPNBR  (1)
00190A          K *NONE   (2)
00200A          K ACTDTE  (3)
     A

Record Format Key Positions


(1) (2) (3)
EMPMST EMPNBR *NONE *NONE
CLSREG EMPNBR CLSDTE *NONE
CLSHST EMPNBR CLSDTE *NONE
JOBHST EMPNBR *NONE JOBDTE
ACTHST EMPNBR *NONE ACTDTE

The records are merged and sequenced as follows:

  1. All records are merged and sequenced by employee number.
  2. For a given employee, records are sequenced by:
    1. The master record (of the EMPMST format)
    2. Records of the CLSREG and CLSHST formats, merged and sequenced together on values of CLSDTE (key position 2)
    3. Records of the JOBHST and ACTHST formats, merged together and sequenced together on values of JOBDTE and ACTDTE (key position 3)

Specifying *NONE in the key definitions achieves this sequencing as follows:

The record sequence defined by the previous key specifications is totally dependent on the order in which the formats are specified. For example, if JOBHST had been specified before CLSHST, key position 2 would read:

*NONE, CLSDTE, *NONE, CLSDTE, *NONE

Here, the values of CLSDTE within CLSREG would not have been sequenced with the values of CLSDTE within CLSHST, and JOBDTE would not have been sequenced with ACTDTE.

DDS Example 4

In Figure 12, assume that an employee has repeated a class. To sequence two records with the same values for EMPNBR and CLSDTE, a third key field, DATE, is specified in record format CLSHST. However, DATE cannot be specified in the next available key position (position 3) because JOBDTE and ACTDTE appear in that position for other formats. If DATE is specified in this position, the attributes of DATE are compared with the attributes of CLSHST and JOBHST, and the key definitions are rejected.

To obtain the sequencing necessary, specify *NONE before DATE, displacing DATE to key position 4.

The DATE field can be shown in position 4 as in Figure 12.

Figure 12. Specifying the Key Field (Example 4)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
00010A          R EMPMST                    PFILE(EMPMSTP)
00020A          K EMPNBR
00030A*
00040A          R CLSREG                    PFILE(CLSREGP)
00050A          K EMPNBR
00060A          K CLSDTE
00070A*
00080A          R CLSHST                    PFILE(CLSHSTP)
00090A          K EMPNBR
00100A          K CLSDTE
00110A          K *NONE  (1)
00120A          K DATE   (1)
00130A*
00140A          R JOBHST                    PFILE(JOBHSTP)
00150A          K EMPNBR
00160A          K *NONE
00170A          K JOBDTE
00180A*
00190A          R ACTHST                    PFILE(ACTHSTP)
00200A          K EMPNBR
00210A          K *NONE
00220A          K ACTDTE
     A

Record Format Key Positions



(1) (2) (3) (4)
EMPMST EMPNBR *NONE *NONE *NONE
CLSREG EMPNBR CLSDTE *NONE *NONE
CLSHST EMPNBR CLSDTE *NONE DATE
JOBHST EMPNBR *NONE JOBDTE *NONE
ACTHST EMPNBR *NONE ACTDTE *NONE

Specifying DATE in key position 4 enables records from physical file CLSHSTP with identical values for EMPNBR and CLSDTE to be merged and sequenced according to the value for DATE.

Note:
Since values are actually placed in the keys to ensure the sequencing in the previous examples, duplicate key values are not always predictable when *NONE is needed for logical files with more than one record format.

Select or omit field name

Use select or 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 13 and Figure 14. 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 13 shows how to specify the select/omit field using ANDed select statements.

Figure 13. 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 13, 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 14 shows how to specify the select/omit field using an omit statement ORed with two select statements ANDed together.

Figure 14. 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 14, records are supplied to the program if they pass both of the following tests:

Figure 15 shows several ways to specify the same select/omit logic.

Figure 15. 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 15, 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 ]