Toad Data Point 5.3 - Release Notes

New Features System Requirements Resolved Issues and Enhancements Known Issues Third Party Known Issues Getting Started Product Licensing Globalization About Us Copyright

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. Considerations and Limitations of Cross-Connection Queries

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

To build a cross-connection query visually

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.
Drag tables and views from the Object Explorer to the Diagram pane.

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.

Build Queries Visually

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

To compose a cross-connection query in the editor

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.

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.

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.

About Editing SQL

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. Using Intelligence Central Functions in Queries

Considerations and Limitations of Cross-Connection Queries

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

Consideration/Limitation	Description
Performance concerns	A cross-connection query may take longer to execute than a query against a single database because result sets from each database are retrieved and combined to generate a single result set. In the Professional edition of Toad Data Point, the enhanced execution engine utilizes several optimizing techniques to improve the performance of cross-connection queries.
ODBC driver support	An ODBC driver can be selected for a single connection through each connection's Properties dialog or globally through Tools \| Options \| Database.
Using an Oracle connection in a cross-connection query	To use an Oracle connection in a cross-connection query, you must install an Oracle ODBC driver. Create Oracle Connections Note: The Oracle Full Install client includes an ODBC driver. However, if you are using the Oracle Instant client (which may not include an ODBC driver), you may need to install an Oracle ODBC driver.
Oracle Direct Connection	You cannot use an Oracle Direct Connection in a cross-connection query.
Using a Teradata connection in a cross-connection query	To use a Teradata connection in a cross-connection query, you must install a Teradata ODBC driver. Create Teradata Connections
Using a MySQL ODBC connection in a cross-connection query	To use a MySQL ODBC connection in a cross-connection query, you must specify a database for the connection. Troubleshoot MySQL Issues
Result sets	You cannot edit result sets.
Oracle Long Data Type	Cross-connection query does not support Oracle long data types or Oracle LOBs.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Toad Data Point 5.3 - Release Notes

Create Cross-Connection Queries

Create Cross-Connection Queries

Considerations and Limitations of Cross-Connection Queries