Chat now with support
Chat with Support

Toad Data Point 5.1 - Release Notes

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

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.

    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.

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.

 

Related Topics

Build Queries Visually

About Editing SQL

Optimize Cross-Connection Queries

Cross-Connection Query Functions

Tutorial: Visually Build a Query

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating