CHAPTER 6: Multiple Passes through Records

The final script of this volume turns the personnel table on its side to illustrate more uses of the MAXWIDTH function and the ability to go through the same set of records several times. That is, instead of the table looking like this:

number     name       phone      credit_limit
value      value      value      value
value      value      value      value
value      value      value      value
value      value      value      value

it now looks like this:

number            value      value      value      value
name              value      value      value      value
phone             value      value      value      value
credit_limit      value      value      value      value

Rotating a table through 90 degrees may sound trivial, but presents several problems when examined in detail. For example, how wide should the new columns be? In order for everything to fit, they must actually be as wide as the widest entry in the entire original table. Similarly, since the records are being printed across the line instead of down the page, there is a definite possibility that the new table will be too wide for the page.

In the script below, all the records are selected from the personnel table, and the maximum width of each attribute is found. These widths are then compared to find the widest, which is then used as the width for all the new columns (so that all the data will fit), plus a couple of spaces between the columns.

We do a simple check that the table will actually fit across the page by finding the number of records and multiplying this by the new column width to find the entire width of the table. If it will not fit, an error message is printed.

The data is printed by going through all the records four times, printing number, name, phone, and credit limit, respectively, on successive passes.

Here is the script:

LET DATABASE = "repairs";
SELECT FROM personnel;

LET numberwidth = MAXWIDTH number;
LET namewidth = MAXWIDTH name;
LET phonewidth = MAXWIDTH phone;
LET creditwidth = MAXWIDTH credit_limit;
LET widest = 0;
IF numberwidth > widest
	LET widest = numberwidth
END;
IF namewidth > widest
	LET widest = namewidth
END;
IF phonewidth > widest
	LET widest = phonewidth
END;
IF creditwidth > widest
	LET widest = creditwidth
END;
LET widest = widest + 2;
LET recno = COUNT name;
IF (recno * widest + 8)<= PAGEWIDTH/* 8 is width of
	Number + 2 */
	PRINT "Number" width 8;
	FOR EACH GROUP OF name
		PRINT number WIDTH widest;
	END;
	NEWLINE;
	PRINT "Name" width 8;
	FOR EACH GROUP of name
		PRINT name WIDTH widest;
	END;
	NEWLINE;
	PRINT "Phone" width 8;
	FOR EACH GROUP OF name
		PRINT phone WIDTH widest;
	END;
	NEWLINE;
	PRINT "Credit" width 8;
	FOR EACH GROUP OF name
		PRINT credit_limit PIC G$FF9V.99" width
		widest;
	END;
ELSE
	PRINT "Table is too wide for page", NEWLINE;
END;

The output is shown below (minus extra blank lines from the end). Only the first four records from personnel are shown, otherwise, the table is too wide for the page!

Number   3               4               5              8
Name     Jones           Scarlatti       Mosca          Peterson
Phone    (415)667-2951   (416)961-7363   (415)426-9681  (415)978-6060
Credit   $500.00         $100.00         $750.00        $250.00