Browsing Master-Detail Data

When doing relational database work (regardless of whether you are a DBA, developer, or business analyst), the time comes when you must be able to inspect and sometimes modify data. Of course, you could use TOAD's SQL Editor window with its data grid, but today's highly normalized databases would make "one table at a time" efforts tedious and error-prone . What's generally necessary is a screen where you can easily see a table, its related tables, and their synchronized data (that is, focus on parent sets and focus on children to the same base record). TOAD has just such a screen in its Master Detail Browser, shown in Figure 9.1 and located on the main menu at Tools, Master Detail Browser.

Figure 9.1. TOAD Master Detail Browser screen.

When you first enter this screen, the data grids will be blank. You must first select a base schema and table for which you want to see the results. For a database schema possessing referential integrity constraints (that is, foreign keys), selecting a table for the topmost data grid automatically results in TOAD populating the related table selections and querying their data. Plus all these grids are kept synchronized. You navigate the grid results, and the lower grids' data are kept completely synchronized to your selections. So with this one screen, you can now inspect and update all your data within the context of its complete and accurate relational environment.

Even though TOAD only retrieves 25 rows at a time for data grids, selecting a parent table that has numerous detail data grids and many rows per grid can result in this screen taking a very long time to initially locate all the candidate rows and then populate all the data grids even for the limited number of rows being returned. Moreover, the related data grid synchronization as you navigate can also slow things down further. Thus TOAD offers the ability to filter the parent table. You simply click the Filter/ Sort main table data button, which will open up the dialog shown in Figures 9.2 and 9.3. This screen offers two tabs: one for sorting and one for filtering.

Figure 9.2. TOAD Master Detail Browser ”Sort tab.

Figure 9.3. TOAD Master Detail Browser ” Filter tab.

After you've made your sort and filter selections, click OK. TOAD will now change the color of the filter toolbar icon to red as an indicator that it's active, and then apply both the filter and sort operations to the first data grid. Figure 9.4 shows the results of using filters and sorts (note how the filter icon is now red indicating that it's active).

Figure 9.4. TOAD Master Detail ”filtered results.

Of course, after you've inspected the data and found those rows that require modification, it's the exact same process as used in the SQL Editor data grid (refer back to Chapter 3). You can make those modifications directly in the data grid by simply selecting the cell and making the change. For numeric fields, a built-in calculator is available, as shown in Figure 9.5. For Date fields, there is a built-in calendar, as shown in Figure 9.6.

Figure 9.5. TOAD Master Detail ”Calculator.

Figure 9.6. TOAD Master Detail ”Calendar.

Plus, you can switch to single-record view mode for editing as shown in Figure 9.7 by merely clicking on the Single Record View icon in the upper-left corner of the desired data grid. Finally, this screen's data grids also offer context menu options for Print Grid and Save as. Refer back to Chapter 3 for more details.