Was this helpful?
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
1001
The C Programming Language
 
C
2
1001
The C Programming Language
 
language
2
1001
The C Programming Language
 
programming
2
|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