HP 3000 Manuals HP 3000 Manuals
Key Data Items
In SORT-MERGE/XL a key data item is a group of alphabetic, numeric, or
alphanumeric characters. The key data item is used by SORT-MERGE/XL as a
reference to find and arrange the data in a specified order. You specify
a key data item by identifying its position (column) in the record and
its length (number of succeeding columns) with the >KEY command.
A record is a continuous collection of related data that is treated as
one unit. A record can consist of more than one line of data in a file.
It is continued to subsequent lines by entering an ampersand (&) as the
last nonblank character on a line.
To define a key data item with the >KEY command, enter:
>KEY 40, 12
This specifies a key data item that begins in the 40th character position
(column) of the record and is 12 characters (columns) long.
You can sort or merge data based on one or more key data items.
Sorting Files By A Single Key Data Item
SORT-MERGE/XL can sort or merge files based on a single key data item
within a record. Figure 2-1 shows three records of data in a file.
Each record is one line in length. Each record contains a person's last
name, first name, occupation, and social security number. The last name
starts in column 1, the first name in column 10, the occupation in column
25, and the social security number in column 40.
NOTE The examples in this chapter contain two extra lines of numbers
(for example, 123456...9). These two extra lines are included to
show that the data is aligned in the columns established as key
data items. These two extra lines will not appear in your file.
_____________________________________________________________
| |
| |
| 1 2 3 4 |
| 1234567890123456789012345678901234567890123456789 |
| |
| WELBY MARCUS PHYSICIAN 242244444 |
| JONES SMOKEY TRUCKER 333887777 |
| SOUSE EGBERT DETECTIVE 123234454 |
| |
_____________________________________________________________
Figure 2-1. Key Data Item Positions
To define the last name as the single key data item to be sorted
alphabetically, enter:
>KEY 1, 9
The result of a sort done on the records shown in Figure 2-1 , based
on the command >KEY 1, 9, is shown in Figure 2-2 Note that the last
names are now arranged in alphabetical order.
_____________________________________________________________
| |
| |
| 1 2 3 4 |
| 1234567890123456789012345678901234567890123456789 |
| |
| JONES SMOKEY TRUCKER 333887777 |
| SOUSE EGBERT DETECTIVE 123234454 |
| WELBY MARCUS PHYSICIAN 242244444 |
| |
_____________________________________________________________
Figure 2-2. Alphabetical Sort By Last Name
To define the social security number as the single key data item to be
sorted numerically, enter:
>KEY 40, 9
The result of a sort done on the records shown in Figure 2-1 , based
on the command >KEY 40, 9, is shown in Figure 2-3 . Note that the
social security numbers are now arranged in ascending numerical order.
_____________________________________________________________
| |
| |
| 1 2 3 4 |
| 1234567890123456789012345678901234567890123456789 |
| |
| SOUSE EGBERT DETECTIVE 123234454 |
| WELBY MARCUS PHYSICIAN 242244444 |
| JONES SMOKEY TRUCKER 333887777 |
| |
_____________________________________________________________
Figure 2-3. Numerical Sort By Social Security Number
NOTE All entries in a file to be sorted for a key data item must start
in exactly the same column. In Figure 2-1 , all last names
start in column 1 and the number of characters must not extend into
the next data item field. Therefore, if Boris Tscherbakhanovski
were added to the list of last names in Figure 2-1 , his last
name would have to be shortened to Tscherbak so it would not extend
into the next key data item field containing first names. If you
want to merge this file with other files, the key data items in all
files must be located in exactly the same position and have the
same data format.
Sorting Files By Multiple Key Data Items
SORT-MERGE/XL allows you to specify more than one key within a record for
sort or merge purposes. For example, the data in Figure 2-1 , can be
arranged according to four different key data items (last name, first
name, occupation, or social security number). The command to specify the
last name as the single key data item to be sorted is:
>KEY 1, 9
This specifies that the key data item to be sorted begins in character
position (column) 1 and is 9 characters (columns) long. The result of a
sort done using the command >KEY 1, 9 is shown in Figure 2-2 .
The command to specify a multiple key data item sort with the last name
as the major key data item, and the first name as the second key data
item, is:
>KEY 1, 9; 10, 14
This specifies that the major key data item to be sorted is located in
character position (column) 1 and is 9 characters long; the second key
data item begins at character position 10 and is 14 characters long.
If there were two identical last names, the sort program would look to
the second key data item to break the tie. Multiple >KEY commands may be
entered one to a line, or all on one line, each separated by a semicolon:
>KEY 1, 9
>KEY 10, 14
>KEY 25, 15
Or:
>KEY 1, 9; 10, 14; 25, 15
If you define multiple key data items with the >KEY command, the priority
of the sort operation is:
* SORT-MERGE/XL treats the first key data item you enter (in this
example the last name) as the major key and sorts that item first.
* If there are two or more items of equal value in the major key
(two identical last names), the key data items are ordered
according to the second data item identified with the >KEY
command.
* In the case of ties on the second data item, the third data item
entered with the >KEY command is used, and so on.
* If two or more records are equal in all key fields, the original
order of the records in the input file(s) is used. (This is not
possible in this example since each person has a unique social
security number.)
For additional information on sorting by single or multiple key data
items, refer to Chapter 3.
Merging Files By Key Data Items
MERGE/XL allows you to combine two or more sorted files into a single,
new file based on one or more key data items. Figure 2-4 shows a
file called SORTED1. SORTED1 contains the three records shown as sorted
in Figure 2-2 .
_____________________________________________________________
| |
| |
| 1 2 3 4 |
| 1234567890123456789012345678901234567890123456789 |
| |
| JONES SMOKEY TRUCKER 333887777 |
| SOUSE EGBERT DETECTIVE 123234454 |
| WELBY MARCUS PHYSICIAN 242244444 |
| |
_____________________________________________________________
Figure 2-4. Sorted File SORTED1
You can merge this data with one or more additional sorted files such as
SORTED2, shown below in Figure 2-5 .
_____________________________________________________________
| |
| |
| 1 2 3 4 |
| 1234567890123456789012345678901234567890123456789 |
| |
| JONES AL POLITICIAN 768098989 |
| SMITH REGGIE OUTFIELDER 436897302 |
| TRUMAN HARRY POLITICIAN 895634409 |
| |
_____________________________________________________________
Figure 2-5. Sorted File SORTED2
If you merge the two files (SORTED1 and SORTED2) based on the command
>KEY 1, 9; 10, 14, the resulting new file (MERGED1) would contain the
information shown in Figure 2-6 .
_____________________________________________________________
| |
| |
| 1 2 3 4 |
| 1234567890123456789012345678901234567890123456789 |
| |
| JONES AL POLITICIAN 768098989 |
| JONES SMOKEY TRUCKER 333887777 |
| SMITH REGGIE OUTFIELDER 436897302 |
| SOUSE EGBERT DETECTIVE 123234454 |
| TRUMAN HARRY POLITICIAN 895634409 |
| WELBY MARCUS PHYSICIAN 242244444 |
| |
_____________________________________________________________
Figure 2-6. Merged File MERGED1
For additional information on merging files, refer to Chapter 4.