Chat now with support
Chat with Support

Toad Data Point 5.0 - User Guide

View Generated Queries

The Query tab displays the SQL statement created by the current selections in the Query Builder. As you modify query criteria, the SQL statement in this tab automatically updates. In addition, if there are dependencies in the Diagram tab that are incomplete, selecting this tab displays a window that prompts you to correct the issues. This is useful for identifying problems with your syntax before executing the SQL statement. When you edit a statement in this tab, the following indicators display:

 

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.

You can also reverse engineer a query sent from the Editor to the Query Builder in this tab. 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 for Reverse Engineering Queries

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 New Columns from Existing Columns 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.

 

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.

Tips:

  • To open the generated query in the Editor, click on the Query Builder toolbar.
  • To execute the query, click on the Query Builder toolbar.

  

Related Topics

Build Queries Visually 

Reverse Engineer Queries

Related Documents