Master/Detail Query
A Master/Detail query is a form of the attached query that selects data into the simple fields of a form (the master row) and also into a table field (the detail rows), then displays a submenu of operations that can be carried out for each master row selected. The first statement is the parent or master query; the second is the child or detail query.
Inclusion of a submenu offers you a choice of operations that can be carried out for each master row selected, and provides the means for displaying the next master row. If you omit the submenu, a single master row and all the details for that master are displayed at once; there is no way to see the next master row.
The Master/Detail query requires this syntax:
formname := select [distinct]
[fieldname =] [tablename.]columnname
{, [fieldname =] [tablename.]columnname}
from tablename [corrname] {, tablename [corrname]}
[where qual ]
[group by columnname {, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
tablefieldname = select [distinct]
[fieldname =] [tablename.]columnname
{, [fieldname =] [tablename.]columnname}
from tablename [corrname]{, tablename [corrname]}
where qual
[group by columnname{, columnname} [having qual] ]
[order by orderfieldname [sortorder]
{, orderfieldname [sortorder] } ]
[begin | {
submenu
end | }]
This syntax differs from that of a simple attached query in the following ways:
• Two queries are specified rather than one.
• Only two assignment statements are allowed, and each must be to a complex structure.
• No semicolon is allowed between the two assignment statements.
• If the master query has no where, group by, or order by clause, then the last tablename in the from clause must be followed by a corrname. You can specify corrname to be the same as the tablename.
An example of a Master/Detail query is shown below:
'Find' = begin
empinfo := select empname, title,
hourly_rate
from staff s
order by empname
tasktable := select task, hours
from tasks
where tasks.name = :empname
order by task
begin
'Cancel' = begin
/* code to cancel the task */
next;
end
'Update Hours' = begin
/*code to change the hours on a task */
end
'Next' = begin
next;
end
'End' = begin
endloop;
end
end;
commit;
clear field all;
end
In this example, the first query selects the master rows into a temporary data set and displays a single row in simple fields on the Empinfo form. The second query displays the details for each master in the tasktable table field. Once the master and detail rows are displayed, Ingres displays this submenu in the window:
Cancel Update Hours Next End
Choosing End returns the application to the previous menu. Choosing Next causes the next statement to be executed. Each time the next statement steps forward to the next master record, the second query runs, based on the value from the empname column of the new master.
Note that no semicolon is used between the two select statements in the query on the Master/Detail join, or between the second query statement and begin statement (or starting brace) of the submenu.
Rowcount for Master/Detail attached queries is similar to row count for attached queries that place values only in simple fields. Rowcount is undefined while the submenu is active.
After the submenu is closed, rowcount returns the number of master rows you are shown (the first master row plus the number of master rows displayed with the next statement). You can use the inquire_forms table to find the number of rows in the table field, and thus determine the number of detail rows for a master.
Last modified date: 11/28/2023