Toad Data Point 5.6 - User Guide

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

Reverse Engineer Queries

One way to build a query is to begin adding columns and creating joins in the Query Builder, and then send the query to the editor to add more complex functionality, such as a UNION join. You can also send a query from the editor to the Query Builder. For example, you inherited a large query when an employee left and you need to edit it to resolve an issue. After looking at the query in the editor for some time, you still cannot decipher it. So, you right-click the statement and select Send to Query Builder. Now that you can visually see how the tables are referenced, you can identify and resolve the issue.

Considerations and Limitations in the Query Builder

Consider the following when reverse engineering a query using the Query Builder:

General	Description
Single statement support	The Query Builder only supports one statement at a time. If you add multiple statements to the Query tab, only the first statement is used. If you make any changes to the statement on the Diagram tab, the other statements are lost.
Quote identifiers	If you selected the Quote Identifiers checkbox in Tools | Options | Database | General and generate a query from the Query Builder in SQL Server, the query cannot be reverse engineered.
ODBC support	You must use ANSI SQL for the query.
Excel support	Copying a generated query and attempting to reverse engineer that query results in an error. This issue occurs because the parser defaults to Oracle syntax, which does not understand quotation marks for fully qualified object names. To avoid this issue, clear the Use fully qualified object names and Use fully qualified column names checkboxes in Tools | Options | Database | Query Builder or manually edit the query.
Non-ANSI joins (Oracle only)	When sending a query from the Editor to the Query Builder, Toad automatically uses ANSI joins. If you use Where clause joins, click to disable ANSI joins. This converts the statement to the correct join.
Where Condition	Description
Where condition does not display in the Criteria grid after sending it to the Query Builder from the Editor	If you send a query that contains a Where condition from the editor to the Query Builder, it displays in a Global Where clause bubble on the Diagram pane instead of in the Where Condition field in the Criteria grid. You can double-click the Global Where Clause bubble in the Diagram pane or can click to edit it. You can also use the Where condition fields in the Criteria grid to add new Where conditions. Although they display in the grid, they are added to the Global Where clause in the Query tab.
Where condition created in the Criteria grid of the Diagram tab disappears	If you define a Where condition in the Criteria grid and edit any part of the query in the Query tab, the Where condition is removed from the Criteria grid and placed in a Global Where Clause bubble. You can double-click the Global Where Clause bubble in the Diagram pane or can click to edit it.
Having Condition	Description
Having condition does not display in the Criteria grid after sending it to the Query Builder from the Editor	If you send a query that contains a Having condition from the editor to the Query Builder, it displays in a Global Having clause bubble on the Diagram pane instead of in the Having Condition field in the Criteria grid. You can double-click the Global Having Clause bubble in the Diagram pane or can click to edit it. You can also use the Having condition fields in the Criteria grid to add new Having conditions. Although they display in the grid, they are added to the Global Where clause in the Query tab.
Having condition created in the Criteria grid of the Diagram tab disappears	If you define a Having condition in the Criteria grid and edit any part of the query in the Query tab, the Having condition is removed from the Criteria grid and placed in a Global Having Clause bubble. You can double-click the Global Having Clause bubble in the Diagram pane or can click to edit it.
Query Builder Diagram Tab	Description
Comments and code regions removed after making changes in the Diagram tab	When reverse engineering a statement from the editor or the Query tab and switching to the Diagram tab, if the statement contains a block comment or code region and you make a change in the Diagram tab, the comment or code region is removed from the statement.
Same column cannot be used in the criteria grid	You cannot use the same column more than once in the criteria grid. Even if you create a separate alias for the second use of the column, that column is created as a calculated field. See Create Calculated Fields for more information.
Execute with different query in the Diagram and Query tabs	If the query in the Diagram tab is different than the query in the Query tab, executing the query executes the SQL in the Query tab instead of the Diagram.
Save with different query in the Diagram and Query tabs	The Query Builder saves both the statement and diagram in the same file. The contents of the statement and diagram can be different without losing changes.

To reverse engineer a query

1. Right-click a statement in the Editor, and select Send to Query Builder.See About Editing SQL for more information.

   or

   Click in the Query Builder to send a statement to the Editor. Skip the remaining steps and continue to edit the query. See About Editing SQL for more information.

   Troubleshooting: If the query cannot be modeled in the Diagram tab, a message displays and the statement opens in the Query tab. This usually occurs because the graphical diagram cannot support some functionality in the statement. You can view an explanation for this in the Output window or by hovering over the syntax with a red underline () in the Query tab. To continue, correct the error and click to apply the change and open the Diagram tab.
   

2. Continue to build your query visually using the diagram.

3. Select the Query tab and make any additional edits. Indicators for an edited statement are as follows:

   	Inserted lines
   	Modified or removed lines
   	Modified characters
   Use the toolbar in the lower-right corner of the Query tab to modify the color used for each indicator.

   Caution:  If you manually enter or update the statement in the Query tab, you must click to model the query in the Diagram tab. If you do not do this and make additional changes in the Diagram tab, any changes you made in the Query tab are lost.

4. Click to execute the query.

Tip: If you have multiple statements in the Editor and you want to send only one to the Query Builder, highlight the statement, then right-click it and select Send to Query Builder.

Related Topics

Build Queries Visually 

Create Cross-Connection Queries

Build a Subquery

In this tutorial you will create a subquery to use to filter your selection of address IDs to only those addresses from specific regions.

To build a subquery

1. Connect to the Toad Sample (Access) database in the Navigation Manager.

2. Select Tools | Query Builder | Query Builder.
3. Drag the ADDRESS table to the Diagram pane.

4. Click these column names in the ADDRESS table to add the columns to the query:

   • ADDRESS_ID
   • REGION_ID
5. To add a subquery:

   1. Select the Where field in the REGION_ID column and click . The Where Condition editor displays.

      

   2. Select the IN operator from the list, and then click Subquery.

      

   3. A child Query Builder window displays for you to use to build the subquery.

      • The child (subquery) Query Builder window includes a Diagram pane, a Criteria pane, and a Queries pane for navigating back and forth between subqueries and the main query.

        

   4. Drag the REGION table to the Diagram pane and select the REGION_ID column.

   5. Select the Where field in the REGION_ID column and click . In the Where Condition editor, select the In comparison operator, select Constant, and select the values 1 and 2.

      

   6. Your subquery window should look something like this:

      

   7. Click to execute the subquery. Select the Results tab to review the result set for the subquery.

6. Click the Query node in the Queries pane to return to the main query window. A call-out in the main query Diagram pane indicates that the statement contains a subquery and identifies the column it is on. Hover over the call-out to view the subquery statement.

   

7. Click to execute the main query.

Related Topics  

Build Queries Visually

Add Subqueries

Edit SQL

The editor combines a powerful SQL and procedure editor into a single interface.

Notes:

• See Editor Shortcut Keys for more information about keyboard shortcuts used in the Editor.
• Toad has implemented a large script mode to improve performance when opening files larger than 2 MB. By default, when you open a large file, some features such as word wrap, syntax highlighting, and the Script Map are disabled in the Editor. When a large script loads, a message indicates that you are in large script mode in the right margin of the Editor. You can cancel large script mode by clearing the option in Tools | Options | Editor | General.
• To show the Script Map, Script Results or Group Execute panes for the current Editor window select Editor | Windows | <pane>.

To edit SQL

1. Select Tools | Edit | SQL Editor.

   Note: You can query the worksheet from the Editor by entering a dollar sign ($) followed by a cell range to limit the results similar to the following statement:

   Select * from (MyWorksheet$A1:C10)

   You can also query the entire worksheet by entering a dollar sign ($) after the worksheet name; however, this returns empty rows and columns in the worksheet.

2. Review the following to help enter SQL:

   • Complete SQL Statements Automatically

   • Explore and Edit Script with Script Map

   • Insert Code Snippets and Create Code Snippets
   • Add Toad Views

   • Recall SQL Statements 

   • Add or Remove Application Code 

   • View Objects at Cursor 

   • Record and Play Macros 

   • Advanced Analytics
   • About Toad Script

3. Click to format the code. See Format SQL for more information.

4. Right-click a block of code in the Editor to see additional actions available in the context menu. For example, you can turn a line into comment, uppercase or lowercase it, surround with snippet, set numbered bookmark, collapse or expand nodes, send to Query Builder and etc. You can also right-click an object (e.g. a table) and manage it as if in the Object Explorer.
5. Hold Alt + left-click and drag to select vertical blocks of text in Editor.

6. Select the block of code in the Editor, and click to validate the syntax.

   Note: Toad undoes any changes to the object and data in the database after executing the script (notice that the Result Sets tab is empty). To modify the object and data, you need to execute the script.

7. Click Fetch n rows to specify the number of rows to initially retrieve when executing the query. You can set a default value for this option in Tools | Options | Database | General. See General Database Options.

8. Click to execute SQL on the current server or multiple servers at one time. See Group Execution of Scripts for more information. In the Editor toolbar, the execute SQL buttons are grouped under . The button will retain the function of the last execute action for the active Editor window. If you switch to another Editor window, the button will change to reflect the last action for that window. For more information, see Execute SQL.

9. Select the block of code in the Editor and click to create a stored procedure. Stored Procedure creation dialog automatically recognizes procedure body and declared parameters.

Tips:

• Toad automatically saves a backup copy of any modified editor file in the Application Data Directory to avoid losing your work. When you restart Toad after an unexpected close, Toad checks the timestamp on the backup file against the original file, and opens the file that has the latest timestamp. The document recovery option is in Tools | Options | Environment | General. See General Environment Options for more information.
• You can save the current connection, SQL script, executed results, filters, and group execute connections/results (if enabled) 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.
• Editor supports floating window mode.
• The script is automatically checked for syntax errors when executing current statement (F9), checking syntax, changing database, or refreshing the Script Map. The syntax errors are listed in the Script Results pane in the Messages tab.

• If you copy and paste a SQL statement from one editor window to another, Toad automatically rewrites the statement to match the syntax of the connection for the second editor window.

Configure Editor Options

You can specify options for the look and behavior of the SQL Editor by using the Toad Options dialog.

To configure Editor options

1. Select Tools | Options.

2. To specify one of the following options, select the corresponding page in the Options dialog.

   Option Description	Page in Options Dialog
   Display the tabs for the current connection only when using Tabbed windows	Select Environment | Interface
   Change background color of the Editor	Select Editor | General
   Use connection color as background color	Select Editor | General
   Position Editor tab headers vertically	Select Environment | Interface
   Set options to display line numbers, line modifications, and general text formatting	Select Editor | General
   Specify options to automatically replace typed text with the correct substitution text	Select Editor | Auto-replace
   Customize code formatting options including headers and layout characteristics	Select Editor | Formatter

Click here to view a video of this feature.