Ingres Database Reference
Document #: US-17326,EN
------------------------------------------------------------------------------
Major subject: analysis Minor subjects: tech_notes
Keywords: report
Abstract:
Master/Multi-Detail Report Writer Techniques - Equivalent
to INGRES Release 5 Technical Note #76 but applies to Release
6 also. In Release 6 you can use .SETUP and .CLEANUP to
create the temporary tables. How to create a report that
has multiple details.
Expert note:
Master/Multi-Detail Report Writer Techniques
============================================
o Overview
o Creating Temporary Tables for Master / 2 Details Reports
o Using Block Mode in Report Writer
o Technique to Avoid Spanning Pages in Block Mode
o Simulating the .NEED Command When You Have Variable Length Blocks
o The Difference in Using SQL as the Query Language
o Technique to Avoid Spanning Pages in Block Mode
o Master with 3 Details
o Summary
Overview
--------
Many INGRES users would like to generate a report that shows the
relationship between a master and multiple details. We will look at a
method of reporting this type of relationship using a library example.
The desired report in this example looks like this:
TITLE OF BOOK
-------------
Author1 Subject1
Author2 Subject2
Subject3
This information comes from three different tables. One containing the
title of the book, one with author information and one with subject
information. Each table has an id that will be used to join the tables
properly.
Book Table
|id |title |
|--------------------------------------------|
| 1001|The C Programming Language |
|--------------------------------------------|
Author Table
|id |name |
|-----------------------------|
| 1001|Ritchie |
| 1001|Kernighan |
|-----------------------------|
Subject Table
|id |subject |
|-----------------------------|
| 1001|C |
| 1001|programming |
| 1001|language |
|-----------------------------|
The Report Writer can get the data being reported from 3 places, a sin-
gle table, a view based on one or more tables, or several tables joined
in a retrieve(QUEL) or select (SQL) statement.
Since multiple queries are not allowed in the Report Writer, we must
organize our data so that all of the data is contained in single table
or view or a single query.
The retrieve that you think would work in this instance gives a cross
product.
retrieve (book.title, author.name, subject.subject)
where book.id = author.id and
book.id = subject.id
sort by title, name, subject
|title |name |subject |
|--------------------------------------------------------------|
|The C Programming Language |Kernighan |C |
|The C Programming Language |Kernighan |language |
|The C Programming Language |Kernighan |programming |
|The C Programming Language |Ritchie |C |
|The C Programming Language |Ritchie |language |
|The C Programming Language |Ritchie |programming |
|--------------------------------------------------------------|
The Report Writer will not generate the desired report with the data
organized in this manner. The Report Writer can print only columns from
the current row of data that it has read from the data table. That is,
Report Writer reads one row of data at a time and once it goes on to the
next row, it can not reach back and use values from a previous row.
Similarly, you can not easily suppress the printing of a column in the
detail section of a report if duplicates exist in the data table.
Creating Temporary Tables for Master / 2 Details Reports
--------------------------------------------------------
The solution is to create a table that has the data organized in a way
the Report Writer can use. The table will be temporary in nature in
that it will have to be recreated each time the report is re-run if the
underlying data changes. The trick is to join each detail table to the
master table without joining the detail tables to each other. The tem-
porary table will also need a code to help the Report Writer know when
to break.
Here are the queries that produce the temporary table needed for the
desired report:
QUEL:
range of b is book,a is author,s is subject
retrieve into tempreport (b.all,a.name,
subject=ascii(" "),code=1)
where b.id=a.id
append to tempreport (b.all,s.subject,code=2)
where b.id=s.id
SQL:
create table tempreport as
select b.id,b.title,a.name,
subject=ascii(" "),code=1
from book b,author a
where b.id=a.id;
insert into tempreport (id,title,subject,code)
select b.id,b.title,s.subject,code=2
from book b,subject s
where b.id=s.id;
The resulting table looks like this:
|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|
|------------------------------------------------------------------------|
v
Using Block Mode in Report Writer
---------------------------------
The report specs would look like this:
.NAME tempreport
.DATA tempreport
.SORT title,code /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.HEADER title
.LEFTMARGIN 0
.ULCHARACTER "="
.UNDERLINE
.PRINT title .NEWLINE
.NOUNDERLINE
.BLOCK /* start a block after printing the master info */
.HEADER code
.TOP /* goto the top of the block each time code changes */
.ULCHARACTER "-"
/* test the value of code and set the margin appropriately */
.IF code = 1 .THEN
.LEFTMARGIN 5
.UNDERLINE
.PRINT "Authors" .NEWLINE
.NOUNDERLINE
.ELSEIF code = 2 .THEN
.LEFTMARGIN 20
.UNDERLINE
.PRINT "Subjects" .NEWLINE
.NOUNDERLINE
.ENDIF
.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
The final report looks like this:
The C Programming Language
=== = =========== ========
Authors Subjects
------- --------
Kernighan C
Ritchie language
programming
Technique to Avoid Spanning Pages in Block Mode
-----------------------------------------------
If we add more data to our base tables so that they look like this:
Book Table
|id |title |
|--------------------------------------------|
| 1001|The C Programming Language |
| 1002|Computer Programming and Arch.|
| 1003|The INGRES Papers |
| 1004|Database Systems |
|--------------------------------------------|
Author Table
|id |name |
|-----------------------------|
| 1001|Ritchie |
| 1001|Kernighan |
| 1002|Levy |
| 1002|Eckhouse |
| 1003|Stonebraker |
| 1004|Ullman |
|-----------------------------|
Subject Table
|id |subject |
|-----------------------------|
| 1001|C |
| 1001|programming |
| 1001|language |
| 1002|programming |
| 1002|assembler |
| 1002|architecture |
| 1002|computer |
| 1003|Database |
| 1003|INGRES |
| 1003|computer |
| 1004|Database |
| 1004|management |
|-----------------------------|
The same queries as before:
QUEL:
destroy tempreport
range of b is book,a is author,s is subject
retrieve into tempreport (b.all,a.name,
subject=ascii(" "),code=1)
where b.id=a.id
append to tempreport (b.all,s.subject,code=2)
where b.id=s.id
SQL:
drop tempreport
create table tempreport as
select b.id,b.title,a.name,
subject=ascii(" "),code=1
from book b,author a
where b.id=a.id;
insert into tempreport (id,title,subject,code)
select b.id,b.title,s.subject,code=2
from book b,subject s
where b.id=s.id;
Will generate this temporary table:
Tempreport Table
|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|
| 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|
|-------------------------------------------------------------------------|
Simulating the .NEED Command When You Have Variable Length Blocks
-----------------------------------------------------------------
When your report will contain many blocks of data (books), it is often
desirable to insure that a block of data does not span a page boundary.
The following Report Writer code shows changes to the .QUERY section and
the the page, and determine if the next block of data will fit.
.NAME tempreport
.QUERY
range of r is tempreport
retrieve (r.all,
num_sub=count(r.subject by r.id where r.subject != ""),
num_auth=count(r.name by r.id where r.name != ""))
.SORT title,code /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.HEADER title
/* The calculation below contains the following variables and constants:*/
/* line_number is the current line number on the page (RW variable) */
/* page_length is the current page length in the report (RW variable) */
/* num_sub is the number of subjects for this book (calculated above) */
/* num_auth is the number of authors for this book (calculated above) */
/* 4 is the number of lines taken up by the headers in each block */
/* These variables are used to determine if there is enough */
/* room left on the page to print the next block of data. */
.IF num_sub > num_auth and
line_number + num_sub + 4 > page_length .THEN
.NEWPAGE
.ELSEIF num_auth > num_sub and
line_number + num_auth + 4 > page_length .THEN
.NEWPAGE
.ENDIF
.LEFTMARGIN 0
.ULCHARACTER "="
.UNDERLINE
.PRINT title .NEWLINE
.NOUNDERLINE
.BLOCK /* start a block after printing the master info */
.HEADER code
.TOP /* goto the top of the block each time code changes */
.ULCHARACTER "-"
/* test the value of code and set the margin appropriately */
.IF code = 1 .THEN
.LEFTMARGIN 5
.UNDERLINE
.PRINT "Authors" .NEWLINE
.NOUNDERLINE
.ELSEIF code = 2 .THEN
.LEFTMARGIN 20
.UNDERLINE
.PRINT "Subjects" .NEWLINE
.NOUNDERLINE
.ENDIF
.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
The Difference in Using SQL as the Query Language
-------------------------------------------------
To produce the same report using SQL as the query language, you will
need to create 2 additional tables before running the report. This is
because SQL handles aggregates differently. The calculations of num_sub
and num_auth in the query section above can not be done the same way in
SQL. When you wish to perform an aggregation on a set of data (called a
set function in SQL),and group some rows together the select clause can-
not include any column not also listed in the group by clause, except as
an argument to a set function. When a select statement contains a group
by clause, any columns listed in the select clause must be single-valued
per group. So you must create the following tables before running the
report. These tables will be joined back to the first temporary table
to produce the report.
create table sub as
select id,num_sub=count(subject)
from tempreport
where subject != ""
group by id;
create table auth as
select id,num_auth=count(name)
from tempreport
where name != ""
group by id;
select * from sub;
|id |num_sub |
|---------------------------|
| 1001| 3|
| 1002| 4|
| 1003| 3|
| 1004| 2|
|---------------------------|
select * from auth;
|id |num_auth |
|---------------------------|
| 1001| 2|
| 1002| 2|
| 1003| 1|
| 1004| 1|
|---------------------------|
The query section of the above report would look like this in SQL:
select t.id,t.title,t.subject,t.name,t.code,s.num_sub,a.num_auth
from tempreport t,sub s,auth a
where t.id = s.id
and t.id = a.id
The remainder of the report spec would be the same.
Master with 3 Details
---------------------
Suppose we want to include an abstract of the book in our report.
Here is an example of the abstract table:
Abstract Table
|id |abstract
|-------------------------------------------------------------------------|
| 1001|C is a general-purpose programming language which features |
| | economy of expression, modern control flow and data |
| | structures, a rich set of operators. |
| 1002|This book is for those who wish to understand the |
| | architecture and operation of computer systems. The |
| | best way to understand a computer's architecture is to |
| | use it, and the best way to use the architecture is to |
| | program at the assembly level. |
| 1003|This book summarizes the work of the INGRES project at |
| | the University of California, Berkeley over the years |
| |1973-1983. |
| 1004|The principle concepts are concerned with relations and |
| | with concurrency. The book describes relations, their |
| | algebra and calculus, and the query languages that have |
| | been designed using these concepts. |
|-------------------------------------------------------------------------|
We would like our report to look like this:
The C Programming Language
=== = =========== ========
Authors Subjects
------- --------
Kernighan C
Ritchie language
programming
Abstract
--------
C is a general-purpose programming
language with features economy of
expression, modern control flow and data
structures, and a rich set of operators.
.
.
.
The queries to create the temporary table would look like this:
IN QUEL:
range of b is book,a is author,s is subject,ab is abstract
retrieve into tempabsrep (b.all,ab.abstract,name=ascii(" "),
subject=ascii(" "),code=3)
where b.id=ab.id
append to tempabsrep (b.all,s.subject,code=2)
where b.id=s.id
append to tempabsrep (b.all,a.name,code=1)
where b.id=a.id
In SQL:
create table tempabsrep as
select b.id,b.title,ab.abstract,name=ascii(" "),
subject=ascii(" "),code=3
from book b,abstract ab
where b.id=ab.id;
insert into tempabsrep (id,title,subject,code)
select b.id,b.title,s.subject,code=2
from book b,subject s
where b.id=s.id;
insert into tempabsrep (id,title,author,code)
select b.id,b.title,a.name,code=1
from book b,author a
where b.id=a.id;
The report specs would look like this:
.NAME tempabsrep
.QUERY
range of t is tempabsrep
retrieve (t.all,
num_sub=count(t.subject by t.id where t.subject != ""),
num_auth=count(t.name by t.id where t.name != ""),
len_abs=length(t.abstract))
.SORT title,code /* title and code will be break columns */
.RIGHTMARGIN 80 /* it is important to set the right margin here */
.HEADER title
/* The calculation below contains the following variables and constants: */
/* line_number is the current line number on the page (RW variable) */
/* page_length is the current page length in the report (RW variable) */
/* num_sub is the number of subjects for this book (calculated above) */
/* num_auth is the number of authors for this book (calculated above) */
/* len_abs is the total number of characters in the abstract (calculated)*/
/* 6 is the number of lines taken up by the headers */
/* 40 is the number of character per line when abstract is printed */
/* These variables are used to determine if there is enough */
/* room left on the page to print the next block of data. */
.IF num_sub > num_auth and
line_number + num_sub + 6 + len_abs/40 > page_length .THEN
.NEWPAGE
.ELSEIF num_auth > num_sub and
line_number + num_auth + 6 + len_abs/40 > page_length .THEN
.NEWPAGE
.ENDIF
.LEFTMARGIN 0
.ULCHARACTER "="
.UNDERLINE
.PRINT title .NEWLINE
.NOUNDERLINE
.BLOCK /* start a block after printing the master info */
.HEADER code
.ULCHARACTER "-"
/* test the value of code and set the margin appropriately */
.IF code = 1 .THEN
.TOP /* goto the top of the block when code is 1 */
.LEFTMARGIN 5
.UNDERLINE
.PRINT "Authors" .NEWLINE
.NOUNDERLINE
.ELSEIF code = 2 .THEN
.TOP /* goto the top of the block when code is 2 */
.LEFTMARGIN 20
.UNDERLINE
.PRINT "Subjects" .NEWLINE
.NOUNDERLINE
.ELSEIF code = 3 .THEN
.BOTTOM /* goto the bottom of the block when code is 3 */
.NEWLINE
.LEFTMARGIN 0
.UNDERLINE
.PRINT "Abstract" .NEWLINE
.NOUNDERLINE
.ENDIF
.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
.ELSEIF code = 3 .THEN
.PRINT abstract(cf0.40) .NEWLINE
.ENDIF
.FOOTER title
.ENDBLOCK /* end the block at the end of the master info */
.NEWLINE
Summary
-------
In this document we have seen how to create temporary tables to
produce reports on master/multi detail data. We have seen how to use
block mode effectively to position the detail data in the appropriate
places. We have seen a technique to avoid spanning pages while in block
mode. A discussion of the differences in reporting using SQL was
presented.
Releases affected: all(all.all) - Releases not affected:
Errors:
Bugs/SIRS:
------------------------------------------------------------------------------
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William