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.

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 | }]

• 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.

'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.