Toad Data Point 5.4 - User Guide

Browse Data in Related Tables

Use the Master Detail Browser to browse data in parent tables and drill-down to data in related child tables. This is useful when you need to analyze or edit data. For example, suppose you need to view a list of all Nick's Flix stores and the inventory of comedy movies at each store. Using the Master Detail Browser, you specify the table that contains the list of stores as the parent table and specify the table that contains comedy movies as the child table to quickly locate comedies and analyze the inventory.

To browse tables using the Master Detail Browser

1. Select Tools | Master Detail Browser (ALT+T+A).

2. Drag one or more tables from the Object Explorer, Object Search, or Project Manager window to the Diagram pane at the top of the Master Detail Browser. Press CTRL+click to select multiple tables at once.

   Notes:

   • A line between tables indicates any existing relationships between the selected tables.

   • At least one parent table (table without any join arrows pointing to it) must be added to the window or an error message displays when returning results.

3. Join Columns. See Join Columns for more information.

4. Select the checkbox beside each column you want to include.

   Note: Any columns included in a join are automatically included in the results.

5. Click (F9) to retrieve data for the parent table initially. To view data in a child table, click + to the left of each row.

   or

   Click to retrieve data in the parent and child tables. If there is more than one child table, a tab displays for each child table.

6. From the data grid, you can:

   • Edit the data and commit your changes to the database.

   • Right-click the data grid and select Send To | option to add the data to a report, chart, or pivot grid.

Tips:

• Click to align tables and snap them to the grid.

• Select the parent table from the drop-down list in the toolbar to view data for a parent table if there are multiple parent tables.

• Click X on the table you want to remove in the Diagram pane to remove a table and any conditions (e.g., join, where clause) associated with the table.

Query

Tutorial: Build a Query Visually

The Query Builder enables you to create a query without writing or editing SQL statements. Even if you are familiar with SQL, the graphical interface makes it easier to create relationships and visualize the query.

Click here to view a video of this feature.

Note: This procedure does not cover all of the possible steps of visually building a query. It only covers the steps required to build a query for the scenario. See Build Queries Visually for more information.

To visually build a query based on this scenario

1. Connect to the database and select Tools | Query Builder | Query Builder.
2. Select the SH schema in the Object Explorer.

3. Drag the following tables from the Object Explorer to the Diagram pane: TIMES, SALES, CUSTOMERS, and COUNTRIES.

   Tips:

   • Select Query Builder | Arrange Tables (ALT+Q+A) to have Toad arrange the tables in the Diagram pane.
   • You can press CTRL and select a table, and then drag the table into the Editor pane. Click the list to create statements.
   • You can also press CTRL and select all of the tables in the Object Explorer, and then drag them into the Diagram pane. Toad automatically arranges the tables.
4. Add the following columns to the query by clicking each column:

   Table	Column
   COUNTRIES	COUNTRY_NAME
   TIMES	FISCAL_YEAR
   SALES	AMOUNT_SOLD

   Tips:

   • To view details for a table in the Diagram, click (F4). If a table is not selected, details for the last selected table display.
   • To remove a column from the query, click or drag column off grid.
5. Complete the following steps to group the data and sum the sales:
   1. Select the Group By field in the COUNTRY_NAME column, and then click +. The Group By field is in the Criteria pane, which is below the Diagram pane.
   2. Select the Group By field in the FISCAL_YEAR column, and then click +.

   3. Select the Aggregate Function field in the AMOUNT_SOLD column and select Sum. This sums all of the sales per fiscal year per country.

      Note: If you select a group by clause or aggregate function for a column, the rest of the columns in the query must also have a group by clause or aggregate function.

6. Complete the following steps to select the fiscal year range:
   1. Select the Where Condition field in the FISCAL_YEAR column and click . The Where Condition window displays.
   2. On the Form tab, select BETWEEN in the Operators field.
   3. Select 1998 in the first Constant field.

   4. Select 2001 in the second Constant field and click OK.

      Note: The SH schema does not have current data. The 1998-2001 years provide the best data for the purposes of this scenario.

7. Complete the following steps to select countries with less than $500,000 in sales:
   1. Select the Having Condition field in the AMOUNT_SOLD column and click . The Having Condition window displays.
   2. On the Form tab, select < in the Operators field.
   3. Select Sum in the Aggregate field.
   4. Enter 500000 in the Constant field and click OK.
8. Select Ascending in the Sort field of the COUNTRY_NAME and FISCAL_YEAR columns.

9. Click .

   The query results display in the Results tab. See View Result Sets for more information.

   

10. To save the query, right-click the Query Builder tab and select Save File.

    You can save a query and any results sets in a Toad editor file (.tef). This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.

11. See About Designing Toad Data Reports for more information about creating a report from this data.

See Tutorial: Build a Subquery for a tutorial on creating a subquery.

See Add Subqueries for more information on how to add a subquery.

Create Cross-Connection Queries

Use the Cross-Connection Query Builder or the Cross-Connection Editor to create a query that combines data from multiple, dissimilar databases. For example, you can create a query that joins managers from a SQL Server database to their employees in an Oracle database. (This is also often called a heterogeneous or cross-database query.) Toad uses an enhanced query engine to implement advanced execution processes that are specially designed to improve the performance of cross-connection queries.

Before creating a cross-connection query using an Oracle, Teradata or MySQL ODBC connection, please see the special considerations listed at the end of this topic. See Considerations and Limitations of Cross-Connection Queries for more information.

Note: This feature is available in the Toad Data Point Professional Edition only.

To build a cross-connection query visually

1. Select Tools | Build Query | Cross-Connection Query Builder.

   Note: A blue banner along the right side of the Diagram pane indicates that you are in Cross-Connection Query mode.

2. Drag tables and views from the Object Explorer to the Diagram pane.

3. Complete the query in the same manner as you would a non-cross-connection query, but note the following exceptions.

   Object Explorer Feature	In Cross-Connection Query Mode...
   Options	Connection Treelist is the only view option available. In this option, connections, databases, tables, and views are displayed in a treelist in the Object Explorer window.
   Filters	Filters are especially useful in cross-connection queries to reduce the number of objects displayed in the treelist in the Object Explorer window.

   See Build Queries Visually for more information.

Note: Review the "Considerations and Limitations of Cross-Connection Queries" section below.

To compose a cross-connection query in the editor

1. Select Tools | Edit | Cross-Connection SQL Editor.

   Note: A blue banner along the right side of the Editor pane indicates that you are in Cross-Connection Query mode.

2. Compose the SQL statement in the same manner as you would a non-cross-connection query, but note the following exceptions.

   SQL Editor Task	In Cross-Connection Query Mode...
   Naming tables and views	To reference a table or view, you must use a cross-connection fully-qualified name. The format for a cross-connection fully-qualified name depends on the database. The following is an example using the SQL Server format: <database connection string>.<database>.<schema>.<table> 'sqlserverconn'.database.dbo.table Note: For the database connection string, use the connection string displayed in the Navigation Manager window for each connection (or in the Name field in Connection Properties). Tip: Copy and paste a SQL statement from a single connection editor to a cross-connection editor and Toad automatically changes the syntax of the statement to include the cross-connection fully-qualified names.
   Using alias names	You must use alias names for tables and columns in a cross-connection query.
   Using code completion	In Cross-Connection Query mode, the scope of available options in code completion expands to include the databases, tables, and views for all the open database connections. Note: For a list of SQL functions to use when composing a cross-connection SQL statement, see Cross-Connection Query Functions. Tip: To expand/collapse a database node in the code completion list, use CTRL+left arrow and CTRL+right arrow.

   See About Editing SQL for more information.

Tips:

• In a cross-connection code completion list, the selected database displays a descriptive tooltip containing database and connection information.
• To learn how to optimize cross-connection queries, see Optimize Cross-Connection Queries.
• Use the Database Diagram tool to create and save cross-connection relationships, and then send the tables with their relationships already created to the Query Builder.
• You can use Intelligence Central functions instead of platform-specific functions in cross-connection queries against Views in Intelligence Central. See Using Intelligence Central Functions in Queries for more information.

Considerations and Limitations of Cross-Connection Queries

Review the following considerations and limitations before creating a cross-connection query.

Related Topics

Build Queries Visually

About Editing SQL

Optimize Cross-Connection Queries

Cross-Connection Query Functions

Tutorial: Visually Build a Query