Chapter 15

The RELATIVE method of access for INTERNAL files

Good news! All the information that you learned in the last chapter can also be applied to this chapter. You will learn how to overcome some of the limits of the sequential access method by using the relative method of file access. Soon you will be able to read any record in an internal file at any time and in any order. Also, this chapter will introduce you to Business Rules!’s powerful sorting capabilities. When you finish you will be able to:

• Define relative file processing and relative record number.
• OPEN a new or existing internal file for relative processing.
• READ, WRITE, REWRITE, and DELETE records by record number in an INTERNAL file.

15.1 Why learn about the RELATIVE access method?

In the last chapter, you learned that the defining characteristic of the sequential access method is that the order of processing records in the file is from beginning to end. After you finish reading or writing a record, the only things you can do is read or write the next record (or restore the file pointer to the beginning of the file and read the first record again). Records must be processed in sequence. This means that the only way to be able to read the 715th record in a file is to read records 1 through 714 first, which is not very efficient.

With the relative access method, you can read the 715th record directly; it can even be the only record that you read between opening and closing the file. This method is sometimes called the random access method because the records can be processed in any order, even in a totally random order.

How does it work? A record is accessed by specifying its position in the file with a number called the relative record number. The relative record number describes the position of the record relative to the first record in the file. The first record in the file is assigned relative record number 1. The second record is relative record number 2. The tenth record is relative record number 10, and so on.

In short, the relative record number corresponds to the sequential position of the record within the file. In a file containing 52 records, the last record could be read directly by specifying a relative record number of 52. To give you a glimpse ahead in this chapter, a statement to do this could be:

220 READ #2,USING F2,REC=52: ADDRESS$, CITYSTATE$, ZIP$

The defining characteristic of the relative access method is that it allows you to access a record independent of other records by specifying its position in the file with a relative record number.

In addition to the time saved by accessing only the records you want, relative processing can be combined with a sorting technique called an address-out sort (explained in a later chapter); this gives your program an economical way to process an entire file in any one of several possible sorted orders.

Quick Quiz: 15.1

1. A relative record number is:

a) The total number of records in a relative file.
b) The number used to specify the position of a record within the file.
c) The total number of records read so far.
d) Something to submit to the Guinness Book of World Records.

2. The main advantage of the relative access method is:

a) It allows sorting of records.
b) It allows any record to be accessed in any order by specifying its position with a relative record number.
c) It allows records to be processed in sequential order.

Answers 15.1

15.2 Opening an existing file for relative access

As usual, the first step in reading information from a file or writing information to a file is to open the file. The only one difference between OPEN statements that open internal files for relative access and OPEN statements that open internal files for sequential access is that the “method of access” portion of the syntax specifies RELATIVE rather than SEQUENTIAL. For example, the OPEN statement for a file named ACCOUNTS.INT:

500 OPEN #5: “NAME=ACCOUNTS.INT”, INTERNAL,INPUT,RELATIVE

The parts above are: Line number, File number, File ID String, File Type, Type of Use, and Method of Access.

Let’s briefly review the parts of this OPEN statement. The file number is an arbitrary number from 1 to 127 and serves as a shortcut for the file name. The only place that the name of the file is mentioned is the file identification string in the OPEN statement.

The next part of the OPEN statement is the type of file; because ACCOUNTS.INT is an internal file, this OPEN statement specifies the keyword INTERNAL. And, because we want to access the records according to their relative positions within the file, and we are only intending to read information, the method of access is specified as INPUT,RELATIVE.

Since you know some of the basics about processing internal files let’s discuss the keyword in more depth. Remember that the next-to-last keyword may be INPUT, OUTPUT, or OUTIN depending on whether you want to use the file for input, output, or both. There is an important relationship between 1) the “type of use” indicated in the OPEN statement and 2) which of the six I/O statements for internal files may be executed without error.

For example, if a program attempts to execute a READ statement on a file that is opened for OUTPUT, an execution error will occur. The error code will be 0721. When you look up this error code, you will find the description “I/O conflicts with OPEN.” The conflict is that when the file was opened for OUTPUT only, the programmer was promising not to try to read from the file. Business Rules! can optimize system performance based on the guarantee that no reading will be done.

Later, when a READ statement appears, Business Rules! issues an execution error because it is not set up to read.

These error messages are also a form of protection against programmer errors of reading from or writing to the wrong file. If the file number is not specified correctly, it is better to get an error than to write information to the wrong file.

The table below lists all possible combinations of the three keywords for type of file usage and the six I/O statements for internal files. For each combination, the table tells whether using that statement with that type of file usage is legal or illegal. In the table, YES indicates the combination is allowed and will not produce an error; similarly, NO indicates the combination is not allowed and will produce an execution error.

	INPUT	OUTPUT	OUTIN
READ	YES	NO	YES
REREAD	YES	NO	YES
WRITE	NO	YES	YES
REWRITE	NO	NO	YES
DELETE	NO	YES	YES
RESTORE	YES	NO/YES	YES

This table applies to all processing of internal files regardless of whether the method of access is sequential, relative, or keyed (to be discussed in the next chapter).

However, there is a special case involving the use of the RESTORE statement when a file is opened for output. When the OPEN statement specifies OUTPUT,RELATIVE or OUTPUT,KEYED a RESTORE statement is illegal (special error code 0711). Do you remember what happens when RESTORE is used on a file opened for OUTPUT,SEQUENTIAL?

If you don’t remember: The RESTORE statement in this case erases all the data in the file.

Quick Quiz 15.2

1. Which I/O statements can be used on a file opened for OUTPUT?

a) READ
b) REREAD
c) WRITE
d) REWRITE
e) DELETE
f) RESTORE

2. Which I/O statements can be used on a file opened for INPUT?

a) READ
b) REREAD
c) WRITE
d) REWRITE
e) DELETE
f) RESTORE

3. Which I/O statements can be used in a file opened for OUTIN?

a) READ
b) REREAD
c) WRITE
d) REWRITE
e) DELETE
f) RESTORE

True or False:
4. A REWRITE statement can be used without error in a file opened for OUTPUT.
5. A DELETE statement can be used without error on a file opened for OUTPUT.
6. A RESTORE statement can be used without error on a file opened for INPUT.
7. A DELETE statement will always cause an error unless the file is opened OUTIN.
8. A REWRITE statement will always cause an error unless the file is opened OUTIN.
9. Any I/O statement can be used without error when a file is opened OUTIN.

Answers 15.2

15.3 The REC= clause

The REC= clause, which can be used in the syntax of all I/O statements except REREAD, tells the Business Rules! system which record should be accessed when the file is opened for relative processing. Since the REREAD statement only processes the last record successfully read, regardless of how it was read, there is no need to use a REC= clause with this statement.

The REC= clause must be the last clause before the colon in an I/O statement. It must begin with REC= followed by any numeric expression. Remember that numeric expressions can be numeric constants or numeric variables (with or without subscripts), and that expressions can also include arithmetic as well as function calls. Some examples of REC= clauses with these different types of expressions are:

500 READ #1, USING 60, REC=12:A$ (Numeric Expression)
510 READ #1, USING 60, REC=N:A$ (Simple Variable)
520 READ #1, USING 60, REC=1.5+2:A$ (Arithmetic)
530 READ #1, USING 60, REC=N-10:A$ (Arithmetic with a variable)
540 READ #1, USING 60, REC=SQR(144):A$ (Function)

In each of the above cases, the Business Rules! system will determine the value of REC=, and then access the record that can be found at the relative record position matching that value. Consider line 500 as an example. The value of REC= is 12, so Business Rules! will go to the 12th record in file #1 to READ it.

Let’s try out some relative file processing. Access your supplemental program entitled RESAMPRO and run the program to write the file RESAMP.INT.

Note: RESAMP.INT contains 20 records, each of which has one 17-character string field and 2 character numeric field to list “This is record #X”, with “X” replaced by the record number.

Type the following lines into BR:

00010 OPEN #1:”NAME=RESAMP.INT”,INTERNAL,INPUT,RELATIVE
00020 READ #1,USING 30,REC=4:RECNUM$,RECNUM
00030 FORM C 17,N 2
00040 PRINT RECNUM$,RECNUM

Business Rules! should print a message on the screen that indicated which record your program accessed. Now change line 20 to the following (try to predict what will happen before you rerun the program):

00020 READ #1,USING 30,REC=1.5:RECNUM$, RECNUM

Which way does Business Rules! round the REC= value before accessing the file: up or down?

BR actually just drops the decimal.

Here’s another prediction case for you. Add the following lines 50 and 70, which does not use a REC= clause, even though the file is opened for relative processing. What do you think will happen when you run the program?

00050 READ #1,USING 30:RECNUM$,RECNUM
00070 PRINT RECNUM$,RECNUM

If you’re not sure why Business Rules! decided to access this particular record, look back to see which record it accessed in the example before this one. The secret is that Business Rules! will automatically access the next sequential record in the file unless you specifically tell it to go to a different one with the REC= clause.

What do you think will happen if line 20 is as follows? Enter the line and see:

00020 READ #1,USING 30,REC=0:RECNUM$

Now, one more time, type in the following line and see what happens:

00020 READ #1,USING 30,REC=34:RECNUM$

Be sure to look up the errors that you received for both the above situations. When you get to the error-handling chapter of this tutorial, you’ll learn that they can both be trapped with the NOREC error condition.

If you wish to find out how many records are in the file RESAMP.INT, add the line 00080 PRINT LREC(1). This system function returns the last record number in the file specified within parentheses.

Quick Quiz 15.3

1. Two of the following six READ statements use improper syntax for relative file processing; which are they?

a) 00010 READ #45,REC=12,USING FORMT:EGGROLL
b) 00020 READ #7,USING AG,REC=85-N*46+T:INSINK
c) 00030 READ #11,USING 600:REC2
d) 00040 READ #2:
e) 00050 READ #4,USING 420,REC=715:MAT REDROCK
f) 00060 READ #128,USING 130,REC=A$:HIGHNUM$

2. When file #3 is open for relative processing and Business Rules! executes the following statement, which record will be read?

00030 READ #3,USING 40,REC=15:PLASMA,DONOR$,BLDTYPE$

a) The first record in the file.
b) The third record in the file.
c) The 15th record in the file.
d) The 40th record in the file.

3. Which I/O statement(s) never use(s) a REC= clause?

a) REWRITE
b) READ
c) DELETE
d) REREAD
e) RESTORE
f) WRITE

4. What happens when a READ statement without a REC= clause is used on a file opened for relative processing?

a) The last record in the file is accessed.
b) The system reads the next sequential record.
c) The system reads the same record it just read.

5. What systems function can you use to find out the last record number in an internal file?

a) RECL(filenum)
b) LREC(filenum)
c) RECNUM(filenum)

Answers 15.3

15.4 Using I/O statements with RELATIVE processing

When used with relative processing, I/O statements that include the REC= option operate a little differently than those that do not. Let’s take a look at each case in detail.

The DELETE and REWRITE statements

DELETE and REWRITE are similar in many ways. You learned in the last chapter that both had to follow a READ or REREAD statement when used with sequential processing. With relative processing however, the use of the REC= clause allows you to bypass the READ or REREAD step. With a REC= clause, both will first perform an implied READ on the specified record, and then operate on the record just retrieved (without a REC= clause, both DELETE and REWRITE must follow either a READ or REREAD).

Let’s use the RESAMP.INT file again to take a look at some examples. You should have determined in the last lesson that the last record in this file was record number 20. Just for fun, let’s delete record number six. First you must open the file for OUTIN,RELATIVE:

00010 OPEN #8:”NAME=RESAMP.INT”,INTERNAL,OUTIN,RELATIVE

Now enter the following DELETE statement:

00020 DELETE #8,REC=6:

Note that there is no USING clause in the above statement; DELETE always marks a record deleted, so a USING clause to specify special formatting or fields is unnecessary. To find out if the DELETE was effective or not, you can add the following READ, FORM and PRINT statements, and then run the program:

00030 READ #8,USING 40,REC=6:RECNUM$,RECNUM
00040 FORM C 17, N 2
00050 PRINT RECNUM$,RECNUM

If the DELETE was effective, you should have received a NOREC error at line 30.

Now let’s try a REWRITE statement on the second record in the file. The string in the record now contains the string and numeric values “This is record #2”. The following REWRITE statement should change it to say “New record #2”. List your program and change line 20 to the following:

00020 REWRITE #8,USING 40,REC=2:”New record # ”,2

Also change line 40 to REC= 2, to read and print your new record.

The WRITE statement

Without the REC= clause, a WRITE statement adds a record to the end of the file. With a REC= clause, the WRITE statement will try to add a new record at the location specified.

When a WRITE statement with a REC= clause tries to write to a record that already exists, an error will occur, because the REWRITE statement should have been used instead.

The only time that you can WRITE to a record which isn’t the last record is when the record that was previously in that position has been deleted. Since we deleted record number 6 in the last section of this lesson, you can now write to that record with the WRITE statement.

Use the CLEAR command to clear all the program lines on your screen (this also closes all open files), and then type in the following OPEN statement. (Remember that the WRITE statement should only be used on files opened for output.)

00010 OPEN #10:”NAME=RESAMP.INT”,INTERNAL,OUTPUT,RELATIVE

Now, to write a new record in position number 6, enter:

00020 WRITE #10,USING 30,REC=6:”New record # ”,6
00030 FORM C 17,N 2

And so that you can see the results of your actions, enter the following three lines and then RUN the program:

00040 OPEN #11:”NAME=RESAMP.INT”,INTERNAL,INPUT,RELATIVE
00050 READ #11,USING 30,REC=6:RECNUM$,RECNUM
00060 PRINT RECNUM$,RECNUM

What you have just done using this example, is to open the same file in two different ways at the same time. Line 10 opens RELSAMP.INT for output, and line 50 opens it for input.

NOTE: that the two OPEN statements refer to the file by different file numbers: the output open is #10, and the input open is #11. The I/O statements in lines 20 and 50 also respect these file numbers: the WRITE statements in line 20 and 50 also respect these file numbers, the WRITE statement refers to #10, and the READ statement refers to #11.

What happens when you try to WRITE to a record that already exists? Try it by listing the program on your screen and changing line 20 to the following. Run the program.

00020 WRITE #10,USING 30,REC=9:”New record # ”,9

Look up the error that you received in the BRwiki. This error occurred because you tried to write to a record that already existed; REWRITE is the statement that you must use when you wish to change an existing record. The error that you received can be trapped by coding the DUPREC (stands for duplicate record) error condition keyword on the end of the WRITE statement.

As we mentioned before, a WRITE statement that does not use a REC= clause will cause the new record to be added to the end of the file with sequential or relative processing. Let’s add a whole series of records to the end of the RELSAMP.INT file.

You may continue to use the same OPEN statement in line 10 of your screen, but you should replace the remainder of the lines with the following:

00020 FORM C 17,N 2
00030 WRITE #10,USING 20:”This is record #”,21
00040 WRITE #10,USING 20:”This is record #”,22
00050 WRITE #10,USING 20:”This is record #”,23
00060 WRITE #10,USING 20:”This is record #”,24
00070 WRITE #10,USING 20:”This is record #”,25
00080 WRITE #10,USING 20:”This is record #”,26

You will not see any immediate results from this program when you run it, but our sample program in the next section will allow you to list all the records that you just added.

The RESTORE and READ statements

Without a REC= clause, the RESTORE statement will position the pointer at the beginning of files which are opened for either INPUTor OUTIN (just as it does with sequential processing). With a REC= clause, the RESTORE statement can move the file pointer to any relative record in a file opened for relative processing (provided the records for those record numbers have been written and not deleted).

Whether the statement includes a REC= clause or not, RESTORE cannot be used with any relative files opened for OUTPUT.

With these thoughts in mind, let’s conduct some experiments. Enter the following OPEN statement for the internal file RESAMP.INT:

00010 OPEN #3:”NAME=RESAMP.INT”,INTERNAL,INPUT,RELATIVE

Now type in the following statement:

00020 RESTORE #3,REC=13:

When this little program is executed, the above RESTORE statement moves the file pointer to the 13th record of file #3. To check this, you could type in the following READ, FORM and PRINT statements and run the program (remember the rule: file processing continues sequentially when no REC= clause is specified in the READ statement):

00030 READ #3,USING 40:RECNUM$,RECNUM
00040 FORM C 17, N 2
00050 PRINT RECNUM$,RECNUM

You have already learned that when the READ statement is used with the REC= clause, the system will try to read the specified record. When no REC= clause is used, as in the above example, the READ statement reads the record at which the file pointer is currently positioned.

After one READ or RESTORE statement with a REC= clause, you may want to have several READ statements without the REC= clause. For example you could list the set of new records that you added to RESAMP.INT with a program such as LISTLAST. This program allows you to enter the record number of the first record that you added to a file. It then prints out that record and all the rest of the records that come after it. This program also uses the REC(filenumber) function to print out the current relative record number at the same time as it prints the record itself.

01100 ! ******************** L I S T L A S T ********************
01200 ! PURPOSE: List last few records at end of a file
01300 ! CREATION DATE 7/28/04 LAST REVISION: 8/28/05
01400 ! Business Rules!
01500 ! *********************************************************
01600 PRINT NEWPAGE
02000 PRINT “Enter Starting Record Number”
02010 INPUT START CONV 2010
02020 OPEN #1:”NAME=RESAMP.INT”,INTERNAL,INPUT,RELATIVE
02030 RESTORE #1,REC=START: NOREC DONE
02040 FORM C 17,N 2
02050 FORM N 5,” -- “,C 17,N 2
02060 AGAIN: READ #1,USING 2040: RECNUM$ EOF DONE
02070 PRINT #255,USING 2050: REC(1),A$
02080 GOTO AGAIN
02090 DONE: CLOSE #1:
02100 PRINT #255: NEWPAGE
02110 END

Lines 2000 and 2010 ask the operator for the number of the first record to be listed. Line 2030 uses a RESTORE statement to move the pointer to the requested record number. The loop from 2060 to 2080 reads the file sequentially (because there is no REC= clause in line 2060) from the initial location of the file pointer (determined in line 2030) until the end-of-file error condition jumps the program out of the loop.

In a sense, LISTLAST combines relative and sequential processing. In case you are wondering why you would open a file with the keyword RELATIVE and then only read sequentially, the answer is that relative processing is needed for the RESTORE statement that places the file pointer in the right spot to begin the sequential processing.

Quick Quiz 15.4

True or False:

1. The REREAD statement can have a REC= clause.

2. In the REC= clause, the equal sign must be followed by either an integer constant or an unsubscripted variable.

3. In relative processing, the RESTORE statement can only be used to position the file pointer at the beginning or end of the file.

4. Relative processing and sequential processing can both be used after a file has been opened with the keyword RELATIVE.

5. Without a REC= clause, the WRITE statement can only write new records at the end of the file.

6. The WRITE statement with a REC= clause can only write to a record number that has been deleted or to the record number after the last record in the file.

7. The DUPREC error condition can occur only on a WRITE statement.

8. The NOREC error condition can occur on READ, DELETE, REWRITE, and RESTORE statements.

Answers 15.4

15.5 Relative processing: A sample program

The sample program in this section creates a new internal file called ACCOUNTS.INT. This file will work in conjunction with the CHEKBOOK.INT file (which you used in the last chapter) to assign an expense category to every deposit or withdrawal from the checkbook. The program user will then be able to group checks into categories like food, clothing, housing, transportation, recreation, contributions, gifts, taxes, income, and so on for as many as 99 accounts which correspond to account numbers in the ACCOUNTS.INT file. The checkbook system also keeps tract of the year-to-date total amount of money spent on each category.

Here is the record layout for the ACCOUNTS.INT file.

Field	Description	Form	Positions
1	ACCOUNT DESCRIPTION	C 15	1-15
2	NUMBER OF TRANSACTIONS	N 4	16-19
3	YTD TOTAL OF TRANSACTIONS	N 12.2	20-31

Our sample program is ACCENTR. It either creates or opens the ACCOUNTS.INT file (depending on whether or not it already exists), and it allows the operator to enter and update account descriptions for category numbers 1 to 99.

00100 ! *********************** A C C E N T R *******************
00200 ! Add or Change Records in ACCOUNT.INT for Checkbook system
00300 ! CREATION DATE: 7/24/04 LAST REVISION: 7/29/05
00400 ! Business Rules!
00500 ! *********************************************************
00600 !
00700 OPEN #1:”NAME=ACCOUNTS.INT,RECL=31,USE”,INTERNAL,OUTIN, RELATIVE IOERR OLDFILE
00800 FOR I = 1 TO 99
00900 WRITE #1,USING FORM1: “”
01000 NEXT I
01400 GOTO MAINLOOP
01500 OLDFILE: ! File already exists
01600 IF ERR=4150 THEN PRINT !:
OPEN #1: “NAME=ACCOUNTS.INT”,INTERNAL,OUTIN,RELATIVE !:
ELSE PRINT “Unexpected Error”;ERR;”at Line”;LINE !:
STOP
01700 !
01800 MAINLOOP: ! Beginning of main program loop
01900 PRINT NEWPAGE
02000 PRINT FIELDS “12,28,C 15”: “Account Number:”
02100 INPUT FIELDS “12,51,N 2,R”: ACCOUNT CONV 2100
02200 IF ACCOUNT <= 0 THEN GOTO DONE
02300 PRINT FIELDS “14,28,C 20”: “Account Description:”
02400 READ #1,USING FORM1,REC=ACCOUNT: DESC$ NOREC ADD
02500 !
02600 UPDATE: ! Change existing record
02700 RINPUT FIELDS “14,51,C 15,R”: DESC$
02800 REWRITE #1,USING FORM1: DESC$
02900 GOTO MAINLOOP
03000 !
03100 ADD: ! Add new record
03200 INPUT FIELDS “14,51,C 15,R”: DESC$
03300 WRITE #1,USING FORM1,REC=ACCOUNT: DESC$
03400 GOTO MAINLOOP
03500 !
03550 form1: form c 15
03600 DONE: ! end of program
03700 PRINT FIELDS “14,28,C 14”: “END OF PROGRAM”
03800 CLOSE #1:
03900 STOP

The first executable line is the OPEN statement in line 700. Notice that this file is opened for relative processing and that OUTIN indicates both input and output. The RECL= clause indicate that this statement is trying to create a new file. If the file already exists, the IOERR OLDFILE on the end of line 700 will trap this error and transfer control to the line label OLDFILE which is line 1500. Line 1600 checks to be sure the error was 4150; if it was, it opens the file as an existing file, then goes to line 1800 (since 1700 is just a remark).

If line 700 succeeds in opening a new file, the program executes the two loops in lines 800 to 1300. Why? In relative processing, the WRITE statement can only write to a record that has already been deleted, or to the next available record at the end of the file. This program first writes 99 records and then deletes them all, so that this program can write to any record from 1 to 99. You might think of this as initializing the file, or reserving space for 99 records to be written in random order.

Notice that the WRITE statement has a USING clause but not a REC= clause, while the DELETE statement has a REC= clause but no USING clause. Why? The WRITE statement is trying to add on to the end clause as long as the REC= clause only accessed the next record at the end of the file. The syntax rules of the DELETE statement never permit a USING clause. The REC= clause is when the DELETE statement does not follow either a READ or REREAD statement.

You should review the overall logic of lines 700 to 1600 to be sure you understand what happens in each of these three cases: the file does not exist, the file exists and the OPEN succeeds, and the file exists but some IOERR error condition occurs other than 4150. Unless the program is stopped by an unexpected IOERR error condition, the program eventually goes to the line label MAINLOOP which is line 1800.

Each time through the loop that begins with the label MAINLOOP, the program erases the screen, prints the prompt “Account Number”, and waits for a number to be input for ACCOUNT. If letters are input instead of numbers, the CONV clause results in line 2100 being executed again. If an account number of zero (or negative) is entered, the program branches to the label DONE and soon ends.

When a positive account number is entered, the message “Account Description” is printed on the screen. The READ statement in line 2400 tries to read in the record for that account number. If the operator enters account number 18, line 2400 tries to read record 18.

If record 18 has not been entered earlier, the READ statement fails with a “record not found” error code of 0057. This error code is trapped by the NOREC ADD clause on the end of the READ statement. The NOREC error condition transfers the program to the line label ADD, which is line 3100. The purpose of lines 3200 and 3300 is to allow the operator to add a new record containing the description of this account. Line 3200 waits for the operator to enter an account description of up to 15 characters. Line 3300 writes a new record to the file with the description in the first 15 positions and blanks for the rest of the record. Then execution continues again at MAINLOOP.

Now let’s back up to line 2400 and consider what happens when record 18 already exists. The READ statement succeeds and the program drops down to the next lines and the label UPDATE. The purpose of lines 2700 and 2800 is to allow the operator to update the description currently on file. The RINPUT FIELDS statement in line 2700 will display the current value of DESC$ (as obtained from the READ statement). The changes (if any) are output to the file in line 2800. Because this record already exists, A REWIRTE statement must be used to change it. Line 2800 outputs the new value of DESC$ to the first 15 positions of record 18 and leaves the other positions in the records unchanged.

Although the REC= clause was necessary with the WRITE statement to make sure that the information gets output to record 18, the REC= clause is not necessary with the REWRITE statement. Without a REC= clause, the REWRITE statement will change the last record read. As usual, the last record read (in this case record 18) is exactly the one that needs to be changed. Although the program would execute correctly with or without the REC= clause in the REWRITE statement, it’s more efficient to leave it out.

Otherwise the system will read the same record all over again when it could just refer back to its temporary buffer for the information instead. This principle will also apply to REWRITE and the KEY clause, which will be discussed in another chapter.

Quick Quiz 15.5

True or False:

1. When a file is opened with the keyword RELATIVE, a WRITE statement can be used without a REC= clause to add a record to the end of the file.

2. The DELETE statement never has a USING clause.

3. When a file is opened with the keyword RELATIVE, if the record specified in the REC= clause has been deleted from the file, a READ statement with a REC= clause will generate a NOREC error condition.

4. When a file is opened with the keyword RELATIVE, after a record has been read, it is more efficient to use a REWRITE statement without a REC= clause (as opposed to a REWRITE statement with a REC= clause) to make changes in the record.

Answers 15.5

15.6 Introducing the SORT command and the PRESORT utility

When working with data files, often the information is listed in no particular order. Other than the relative record numbers set in place by BR, nothing about the file appears orderly to the user. Of course, sometimes the user will want to order that information, perhaps by client name, date, order type, invoice amount or any other parameter. The best way to do this is to use INDEX files, which will be described in the next chapter. However, often old programs will use the SORT command instead (in order to save hard drive space in the era of 10 KB space limits). A brief explanation of SORT follows:

Business Rules! can order internal files when you use the SORT command to organize the records in a file in a certain order. The file itself must always remain in the same order according to the relative record numbers. The individual fields in the record are the particular items that a user will want to look for. The sort control file, which you will learn more about soon, tells BR which item you would like to SORT by and how.

A file may be sorted or rearranged in ascending or descending order, or by any numeric field or character field. Two or more fields could even be used in a SORT. For example, a file could first be sorted alphabetically by state, and then sorted by city within each state. The SORT command can sort on as many as 10 fields with any combination of ascending or descending order specified for each field individually. For instance, a file containing sales records could be sorted in ascending order by state and in descending order by dollar amount of each sale.

The syntax for the SORT command is as follows

SORT Control file-ref

The program will access an internal file (named by the “Control file-ref”) with the specifications for how the information from the input file should be sorted, and sort it accordingly.

Another feature of SORT is record selection. This means that you don’t have to sort all the records, but can specify rules for including or omitting certain records. For example, you could omit sales records for amounts less than $100. Excluding records is a good way to speed up the execution of a sort. The fewer records to be sorted, the faster the sort will get done!

What do you have to do to sort a file? The SORT command needs to know only one thing: the name of the sort control file. The sort control file contains instructions which guide or control the sort. So, how do you set up a sort control file? Write your own, or use a PRESORT utility program.

In general, the purpose of the PRESORT utility program is to guide you through a series of screens that help you create and modify sort control files.

A sort control file is an internal file. The main thing that PRESORT does is to ask you questions, and then write the answers to an internal file according to the rules required by the SORT command. You could write you own Business Rules! program to create or modify a sort control file. However, beginners and also many experienced programmers prefer to use a ready-made PRESORT.

Quick Quiz 15.6

True or False:

1. Business Rules!’s SORT command can only be used with internal files.

2. Before using the SORT command, it must be loaded with a LOAD command.

3. The SORT command only allows sorting in ascending order.

4. A file can be sorted on 10 different fields with just one SORT command.

5. A file can be sorted on one field in ascending order and then another field in descending order with just one SORT command.

6. Record selection means that the sort must include all records.

Answers 15.6

15.7 All about the PRESORT utility

First, let’s look at some definitions of terms that are used in sorting in general and PRESORT in particular.

Sort Control file - Also called the control file. A file containing the instructions used to guide the sorting process. This file is usually created by PRESORT. This filename is the only parameter in the SORT command, and appears after the keyword SORT. This tutorial arbitrarily uses .SC for the first two letters of the extension for sort control files.

Input file - Also called the master file or the original file. The data file containing the records to be sorted; usually these records are in no particular order.

Output file - The file that will contain the output of the sort, that is, the information in rearranged order as specified in the sort control file. Two types of sort output files are record out and address out files.

Record Out Sort File - One of two types of output files from a sort. The records in this type of file are entire copies of the records in the original input file, but rearranged in a new order. The record length of a record out file is always the same as the record length of the input or master file. This tutorial arbitrarily uses the extension .REC for record out sort files.

Address Out Sort File - One of two types of output files from a sort. The records in this type of file are numbers corresponding to relative record numbers of the records in the original input file. The order of these record numbers or addresses is used to indicate the rearranged new order produced by the sort. Because these record numbers are always written with a PD 3 conversion specification, the record length of an address out file is 3, regardless of the record length of the input or master file. This tutorial arbitrarily uses the extension .ADR for record out sort files

Record Out Sort - Any sort that produces a record out sort file. This tutorial arbitrarily uses the extension .SCR for any sort control file that performs a record out sort.

Address Out Sort - Any sort that produces an address out sort file. This tutorial arbitrarily uses the extension .SCA for any sort control file that performs an address out sort.

End-Chapter Exercise

To practice this, let’s sort the CHEKBOOK.INT file to arrange all the checks by account number from 1 to 99. Also, within each account, let’s arrange the checks from largest amount to smallest amount. No deposits should be included, and no withdrawals that are not checks. In other words, let’s exclude everything without a check number. Either using a PRESORT program or on your own, do this now.

Solution

Quick Quiz 15.7

True or False:

a) An address out sort file requires less disk space than a record out sort file when sorting the same records in the same way (if the record length of the input file is bigger than 3).

b) An address out sort will execute faster than a record out sort when sorting the same records in the same way (if the record length of the input file is bigger than 3).

Answer:
1.) T
2.) T

NEXT:Chapter 16

Back to Index