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.
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.
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...|
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 are especially useful in cross-connection queries to reduce the number of objects displayed in the treelist in the Object Explorer window.
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:
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.
Review the following considerations and limitations before creating a cross-connection query.
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.
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. |
|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. |
You cannot edit result sets.
|Oracle Long Data Type||Cross-connection query does not support Oracle long data types or Oracle LOBs.|