Create Cross-Connection Queries

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

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.

   Build Queries Visually

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.

   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.

Related Topics

Build Queries Visually

About Editing SQL

Optimize Cross-Connection Queries

Cross-Connection Query Functions

Tutorial: Visually Build a Query