How to Avoid Awkward Page Breaks
Assume you have invested in a new bookcase and have expanded the size of your personal library by many volumes. Now when you combine your three tables, you create a much larger union than before:
|id |title |name |subject | code|
|----|------------------------------------|--------------|---------------|-------|
|1001|The C Programming Language |Kernighan | | 1|
|1001|The C Programming Language |Ritchie | | 1|
|1002|Computer Programming and Arch. |Eckhouse | | 1|
|1002|Computer Programming and Arch. |Levy | | 1|
|1003|The INGRES Papers |Stonebraker | | 1|
|1004|Database Systems |Ullman | | 1|
|1005|The Quiet American |Greene | | 1|
|1001|The C Programming Language | |C | 2|
|1001|The C Programming Language | |language | 2|
|1001|The C Programming Language | |programming | 2|
|1002|Computer Programming and Arch. | |architecture | 2|
|1002|Computer Programming and Arch. | |assembler | 2|
|1002|Computer Programming and Arch. | |computer | 2|
|1002|Computer Programming and Arch. | |programming | 2|
|1003|The INGRES Papers | |Database | 2|
|1003|The INGRES Papers | |INGRES | 2|
|1003|The INGRES Papers | |computer | 2|
|1004|Database Systems | |Database | 2|
|1004|Database Systems | |management | 2|
|1005|The Quiet American | |Vietnam | 2|
|----|------------------------------------|--------------|---------------|-------|
If you create a report from such variable blocks of data, you must issue specific instructions to Report-Writer about where and where not to place page breaks; otherwise, you find that some of your data has been incongruously parceled across two pages. In cases where you use Report-Writer to generate a report from a single, unjoined table, you would use a simple .need statement to establish proper page breaks..
In this case, when generating a report from a joined table, you must use variable parameters to the .need statement to assure proper page breaks, as follows:
1. Change the selection statement in your .query section to a creation of a view in your .setup section.
2. Create two new tables in your .setup section based on that view which contain information about the number of authors or subjects per book.
3. Join the two new tables with the view in the .query section.
4. Alter the report specifications to use the new information for paging.
Note: The .setup and .cleanup sections can only use the SQL language. For the QUEL implementation of this example, see the QUEL Users Notes section below.
The reason for the three-step process is that SQL requires a special method for the calculation of num_sub and num_auth. In SQL, when you perform a set function on a set of data and group rows together, you cannot place in the select clause any column not also listed in the group by clause, except as an argument to a set function. When a select statement includes a group by clause, any columns listed in the select clause must be single‑valued per group.
Here is the revised specification file for the report, with the new .setup, .cleanup and .query sections:
.NAME books2
.OUTPUT books2.out
.LONGREMARK
The BOOKS2 report demonstrates using setup and cleanup to
produce temporary tables.
.ENDREMARK
.SETUP
create view tempbooks as
select b.id, b.title, a.name, '' as subject, 1 as code
from book b, author a
where b.id = a.id
union
select b.id, b.title, '' as name, s.subject, 2 as code
from book b, subject s
where b.id = s.id;
create table subj_count as
select id, num_sub=count(subject)
from tempbooks
where code = 2
group by id;
create table auth_count as
select id, num_auths=count(name)
from tempbooks
where code = 1
group by id;
.CLEANUP
drop tempbooks;
drop subj_count;
drop auth_count;
.QUERY
select b.id, b.title, b.name, b.subject, b.code,
a.num_auths, s.num_sub
from tempbooks b, subj_count s, auth_count a
where b.id = a.id and b.id = s.id
.PAGELENGTH 20
.SORT title, code
.BREAK title, code /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.DECLARE
lines_in_title = integer,
authors_column = integer,
subject_column = integer,
title_string = varchar(8)
.HEADER report
/* Initialize variables */
.LET lines_in_title = 4
.LET authors_column = 5
.LET subject_column = 20
.HEADER title
/* Request the maximum number of lines needed to print all */
/* book information on one page. */
.IF num_sub num_auths .THEN
.need num_sub + $lines_in_title
.ELSE
.need num_sub + $lines_in_title
.ENDIF
/* Reset margin and print title of book */
/* start a block after printing the master info */
.LEFTMARGIN 0
.ULCHARACTER '='
.UNDERLINE
.PRINT title .NEWLINE
.NOUNDERLINE
.BLOCK
.HEADER code
/* goto the top of the block each time code changes */
/* set the margin to the correct column for the code type */
.TOP
.ULCHARACTER '-'
.IF code = 1 .THEN
.LEFTMARGIN $authors_column
.LET title_string = 'Authors'
.ELSEIF code = 2 .THEN
.LEFTMARGIN $subject_column
.LET title_string = 'Subject'
.ENDIF
.UNDERLINE
.PRINT $title_string .NEWLINE
.NOUNDERLINE
.DETAIL
/* test the value of code to see which column to print */
.IF code = 1 .THEN
.PRINT name .NEWLINE
.ELSEIF code = 2 .THEN
.PRINT subject .NEWLINE
.ENDIF
.FOOTER title
.ENDBLOCK /* end the block at the end of the master info */
.NEWLINE
Computer Programming and Arch.
======== =========== === ====
Authors Subject
------- -------
Eckhouse architecture
Levy assembler
Computer
Programming
Database Systems
======== =======
Authors Subject
------- -------
Ullman Database
Management
The C Programming Language
=== = =========== ========
Authors Subject
------- -------
Kernighan C
Ritchie language
Programming
The INGRES Papers
=== ====== ======
Authors Subject
------- -------
Stonebraker Database
INGRES
Computer
The Quiet American
=== ===== ========
Authors Subject
------- -------
Greene Vietnam
QUEL User Notes for a Join
When you generate a report from a joined table, you must simulate the .need statement to assure proper page breaks. In QUEL, this involves making the same alterations to the report specifications file as shown in the text, but no additional tables need to be constructed.
Here is the query statement for the final report specification:
.NAME booksq
.OUTPUT booksq.out
.LONGREMARK
The BOOKSQ report uses previously executed QUEL
statements to create a temporary table,tempbooksq
which is the join of books, authors and subject.
.ENDREMARK
.QUERY
range of b is tempbooksq
retrieve (b.all,
num_auths=count(b.subject by b.id where
b.code=1),
num_sub=count(b.subject by b.id where b.code=2)
)
...
Last modified date: 01/30/2023