The detail functionality enables the user to simultaneously represent and evaluate two tables which are related to each other (master-detail).
The relation between two tables is described either explicitly by join conditions or implicitly by FOREIGN KEYs.
In both cases, it is possible to find for a row of one of the two tables (master table) the corresponding rows in the respective other table (detail table) and to represent these rows simultaneously. Consequently, a 1 : n representation follows from both tables.
The master-detail representation displays two tables in report format. In the upper part of the screen, a row of the master table is displayed. In the lower part of the screen, those rows of the detail table are displayed which match the master row above.
To switch between the two tables, press the 
or 
key.
If another row is selected in the master table, the attempt is made to find the corresponding rows in the detail table which match the new master row. If no detail rows are found, a corresponding message is returned.
As the whole DETAIL command is too long for the command line, the DETAIL call opens a special input form where the user can enter the DETAIL select. If the command is not called from the report generator, the DETAIL select must be enclosed in the keywords DETAIL/ENDDETAIL.
The syntax of the DETAIL call is as follows:
DETAIL OFF returns from the master-detail representation to the normal representation.
Example with FOREIGN KEY condition:
MASTER
SELECT "master table" ( * )
FROM customer
DETAIL
SELECT "detail table"
( rno, cno, hno, roomtype)
FROM reservation
FOREIGN KEY customer_reservation REFERENCES customer
ORDER BY hno
REPORT
WIDTH *
NUMBER on
ENDDETAIL
REPORT
WIDTH *
and with JOIN condition:
MASTER
SELECT "master table" ( * )
FROM customer
DETAIL
SELECT "detail table"
( rno, cno, hno, roomtype)
FROM reservation
WHERE cno = :cno
ORDER BY hno
REPORT
WIDTH *
NUMBER on
ENDDETAIL
REPORT
WIDTH *
Comments
1. Because two result tables are simultaneously processed, they must be named; otherwise, errors may occur.
2. A JOIN condition always contains the identification (name or number) of a master table column on the right side. Column identifiers that do not occur in the master select list cause syntax errors.
3. When a FOREIGN KEY is used, only those relationships are inserted into the detail WHERE clause for which the master column was selected.
4. ORDER BY and GROUP BY statements within the detail SELECT statement must be placed after the FOREIGN KEY or JOIN condition.