Reverse Engineer Queries
One way to build a query is to begin adding columns and creating joins in the Query Builder, and then send the query to the editor to add more complex functionality, such as a UNION join. You can also send a query from the editor to the Query Builder. 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 in the Query Builder
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, SQL Server, and Teradata) |
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 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 |
|
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 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 |
|
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 |
|
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. |
to edit it.
to edit it.To reverse engineer a query
-
Right-click a statement in the Editor, and select Send to Query Builder.See About Editing SQL for more information.
or
Click
in the Query Builder to send a statement to the Editor. Skip the remaining steps and continue to edit the query. See About Editing SQL for more information.
Troubleshooting: If the query cannot be modeled in the Diagram tab, a message displays and the statement opens in the Query tab. This usually occurs because the graphical diagram cannot support some functionality in the statement. You can view an explanation for this in the Output window or by hovering over the syntax with a red underline (
) in the Query tab. To continue, correct the error and click
to apply the change and open the Diagram tab.
-
Continue to build your query visually using the diagram.
-
Select the Query tab and make any additional edits. Indicators for an edited statement are as follows:
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.
Caution: If you manually enter or update the statement in the Query tab, you must click
-
Click
to execute the query.
Tip: If you have multiple statements in the Editor and you want to send only one to the Query Builder, highlight the statement, then right-click it and select Send to Query Builder.
Create Cross-Connection Queries
Build a Subquery
In this tutorial you will create a subquery to use to filter your selection of address IDs to only those addresses from specific regions.
To build a subquery
-
Connect to the Toad Sample (Access) database in the Navigation Manager.
- Select Tools | Query Builder | Query Builder.
- Drag the ADDRESS table to the Diagram pane.
-
Click these column names in the ADDRESS table to add the columns to the query:
- ADDRESS_ID
- REGION_ID
- To add a subquery:
-
Select the Where field in the REGION_ID column and click
. The Where Condition editor displays.
-
Select the IN operator from the list, and then click Subquery.
-
A child Query Builder window displays for you to use to build the subquery.
-
The child (subquery) Query Builder window includes a Diagram pane, a Criteria pane, and a Queries pane for navigating back and forth between subqueries and the main query.
-
-
Drag the REGION table to the Diagram pane and select the REGION_ID column.
-
Select the Where field in the REGION_ID column and click
-
Your subquery window should look something like this:
- Click
to execute the subquery. Select the Results tab to review the result set for the subquery.
-
-
Click the Query node in the Queries pane to return to the main query window. A call-out in the main query Diagram pane indicates that the statement contains a subquery and identifies the column it is on. Hover over the call-out to view the subquery statement.
- Click
Set Bind Variables
Toad supports the use of bind variables (bind parameters) in SQL statements. When you execute a statement containing a bind variable, Toad prompts you to enter a value.
Toad also allows you to specify a default value for the variable before the query is executed by using the Variables option. The default value can be specified by entering the value manually or by using advanced options to configure a list of specific values.
If bind variables are defined in the query, clicking on the Variables button will bring up a new window where all the variables present in the SQL script are listed.
If there are no variables in the SQL script, clicking on the Variables button will raise the following message:
Automation Scripts. To learn about entering variable values for Automation scripts at run time, see the Set Variable and Send Email activities in Use System Automation Activities.
Publish with Variables. To learn how to use variables when publishing to Intelligence Central, see Variables in Views/Scripts and Publishing to Intelligence Central.
Toad supports the following bind variable formats:
-
Colon (:variable)
Example: SELECT * from employee WHERE user = :user;
-
At symbol (@variable) in SQL Server connections
Example: SELECT * from employee WHERE user = @user;
-
Question mark (?)
Example: SELECT * from employee WHERE user = ?;
-
Dollar sign ($) in PostgreSQL connections
Example: Select * from employee WHERE user = $1;
Toad also supports these variable formats:
-
Double ampersand (&&variable) as a literal replacement SQL*Plus format in Oracle connections
Example: SELECT * from employee WHERE user = '&&user';
Notes:
- Avoid using the same symbol to represent a bind variable and statement delimiter in the same SQL statement.
-
In the Editor, click
to enable/disable binding variables in the SQL. When disabled, Toad does not scan SQL for parameters. This button is enabled (depressed) by default.
To set the default value(s) for the bind variable before the query is run
-
In the Editor, compose a query that contains bind variables.
-
Click on the Variables toolbar icon
.
Note: The Bind Variables dialog does not display if the bind variables option is disabled.
-
Enter the bind variable information. Review the following for additional information:
Name
Displays the variable name in the form of a clickable link that brings up the Define variable prompt values options for creating a list of predefined variable values.
Description Enter the description.
Type
Select the data type for the variable from the drop-down list.
Default value
Specify the default value(s) that will be used every time the query is run by:
-
Entering the value(s) for the variable manually
-
Or selecting the value(s) from the predefined list by clicking on the button and choosing the particular value(s)
Note:
-
If a variable has been defined with an IN\NOT IN operator, multiple values can be entered by using the semicolon as a delimiter (each value needs to be separated by a semicolon).
-
The predefined list of values list needs to be configured in the Define variable prompt values before using this option
-
Custom list of values for bind variables
Toad enables you to define a list of specific values that can be used for a bind variable in a query that has been created in the Query Builder or Query Editor.
TThe selected values from the list can be changed at any point in time (before and during query execution) in Toad Data Point/Toad Workbook or Toad Intelligence Central (if the objects are published).his list of predefined values will be saved in the Query Builder or Query Editor file and transferred as a part of any downstream step available in Toad (Pivot, Transform and Cleanse, Dimensional View…).
The selected values from the list can be changed at any point in time (before and during query execution) in Toad Data Point/Toad Workbook or Toad Intelligence Central (if the objects are published).
To create a list of predefined variable values
To access the options for creating a list of predefined variable values, click on the variable name link that will open the Define variable prompt values window.
The following options for defining a list of variable values are available:
-
Specify value(s)
This option enables you to define a list of variable values manually by entering multiple values separated by a semicolon.
Toad also allows you to load the variable values from a file by clicking on the ellipsis button (…) and selecting a specific .txt or .csv file.
Note: Each value in the .txt or .csv file needs to be placed in a new line and no other delimiter should be specified.
-
Get values from the column
This option enables you to create a predefined list of values that contains all the values from a particular column. The column is selected from the drop-down which lists all the columns defined in the query.
-
Get values from the SQL script
Clicking on the Edit Query link will open a mini editor in the SQL Script Edit window. This option allows the user to specify a SQL query that will return a result set for a single column which will be used to populate the list of predefined variable values. After the query is specified, it needs to be run in the editor in order to populate the predefined values list.
The query can be entered manually in the editor or it can be loaded from a Query Builder (.tsm) or SQL file (.sql, .tef) by using the Open button.
If the specified query returns a result set with more than one column, it will not be valid and the following message will be displayed (the same message will appear if the query is not run).
Additional Define variable prompt values options
The Define variable prompt values also provides additional options:
-
Allow user to select multiple values – This option is selected by default and it enables the use of multiple values in the specific variable, meaning that multiple values can be selected for a single variable when using the IN\NOT IN operator.
If this option is deselected, only a single value can be chosen from the list and passed as a variable value.
-
Restrict variable value(s) only to list – This option enables the user to restrict the variable values to the predefined list which is created in the Define variable prompt values options. That means that the values cannot be entered manually and can only be selected from the predefined list of values.
Predefined Variable Value List
If the predefined list has been configured in the Define variable prompt values, it will be available in the Variables window within the Default value field, by clicking on the 
When the
-
A Search box (field) that enables the user to search or filter for specific value(s)
-
The list of values that have been defined in the Define variable prompt values options. Depending on the settings that have been applied, the values will be displayed next to a checkbox (with the Allow user to select multiple values selected) that allows for the selection of multiple values (as shown in the image above) or next to a radio button (with the Allow user to select multiple values deselected), which allows for the selection of a single value.
-
The Refresh button which can be used to refresh the predefined list of values if they are obtained through Get values from the column or Get values from the SQL script options.
To use a list of predefined variable values in Toad Automation
In Toad Automation, the options for creating a list of predefined variable values are available when using Set Variable from System Activities as an automation step.
To enable these options, the Prompt during run checkbox needs to be selected. After the checkbox is ticked, the Define variable prompt values link appears along with a field for selecting the connection that will be used to run the query that populates the list of predefined values for the specific variable.
Clicking on the Define variable prompt values link opens a new window that contains options for defining a list of variable values.
The options and functionality are identical to those that are found in Toad Query Builder, Editor and Workbook with the exception of the Get values from the column option, which is not available in Toad Automation.
To use a list of predefined variable values in Toad Workbook
To access the options for defining a custom list of variable values in Toad Workbook, you need to click on the Variables button, which will open the Workbook Variables dialog.
Options and functionality for creating a list of predefined variable values match the ones found in the Query Builder and Query Editor.
Workbook Variables defined with the same name
If a variable is defined in a specific Workbook Workflow with a custom list of predefined values, this list of variable values (and the option which is utilized to populate the list) will be used for any subsequent variables that are defined with the same name regardless of the Workflow that is currently selected or changes that have been made in the Workflow order.
For example – a variable is defined as variable1 in Workflow 2 and a predefined list of values has been created by using the option Get values from the column.
If another variable is defined with the same name (variable1) in Workflow 1, there will be only one variable present with that particular name in the Workbook Variables dialog, and the values that were initially defined in Workflow 2 will also be used in Workflow 1.
The Workbook Variables dialog contains information about all the Workflows that are using a certain variable in the Used in Workflows column.
To set a bind variable during query run time
-
In the Editor, compose and execute a query that contains bind variables. Toad immediately displays the Bind Variables dialog.
Note: The Bind Variables dialog does not display if the bind variables option is disabled.
-
Enter the bind variable information. Review the following for additional information:
Include
An option for including\excluding a specific bind variable at run time. The include option is selected by default.
Name Displays the name of the bind variable that has been defined in the query.
Description Enter the description.
Type Select the data type for the variable from the drop-down list.
Direction
Select the direction of variable from the list.
Value
Specify the values for the run by:
-
Entering the value(s) for the variable manually
-
Or selecting the value(s) from the predefined list by clicking on the button
-
Or loading the values from a specific .txt or .csv file by clicking on the ellipsis (…) button and selecting the appropriate file.
Note:
-
If a variable has been defined with an IN\NOT IN operator, multiple values can be entered by using the semicolon as a delimiter (each value needs to be separated by a semicolon).
-
If you selected a direction of OUT or RETURN, this value is ignored.
-
If default values have not previously been set in the Variables option, this field will be populated with values that have been specified in the last run.
-
If default values have previously been configured, any changes made will apply only for the current run.
-
If the values for the run are loaded from a .txt or .csv file, each value in the .txt or .csv file needs to be placed in a new line and no other delimiter should be specified.
-
To use a bind variable in the Query Builder
-
In the Where, Having, or OR Condition editor, enter the bind variable in place of a constant.
To store a value for a bind variable in a global variable
Toad allows you to store a value for a bind variable by defining a global variable. Then, when you execute a SQL statement containing that bind variable, Toad will not prompt you for a value. You can access this feature from the Project Manager or the Toad Views manager.
- Click
in the Project Manager or Toad Views Manager toolbar.
- Enter a name and a value for the bind variable, and specify a data type.
Tip: After you store a bind variable, it is available to be used as a variable in Automation. The stored bind variables appear in the list of variables in the expression editors in various activities.
Note: Defining and using a custom list of values for bind variables is not supported for global variables.
Use Quote Function to Substitute a String at Run Time
In addition to supporting the use of bind variables, Toad also provides a way for you to easily substitute a string, such as a database name or table name, in your SQL statement at execution time. This method uses the Quote() function, one of Toad's built-in functions (known as Toad Script) for SQL statements. The Quote function can insert the value of a bind variable as a literal value before the statement is sent to the server. This is useful if you want to use bind variables in places where they are not allowed. See About Toad Script for more information.
For example, suppose you want to create the same table in several different databases, but you do not want to re-write the SQL statement for each database. You can use one statement that includes the Quote() function in place of the database name.
Create table {{Quote(:dbname)}}.NewTable as select * from dual
When you execute the statement, Toad prompts you to enter a string value in place of {{Quote(:dbname)}}. You then enter the database name.
To use the Toad Quote function to substitute a string at run time
- Enter a SQL statement in the Editor.
-
Use the following syntax in place of the string that you want to substitute.
{{Quote(:name)}}
For example:
Create table {{Quote(:name)}} as select * from dual
- Execute the statement. The Bind Variable dialog opens.
- Enter a string to use in place of :name and click OK.
- Toad inserts the string (quoted if necessary) into the SQL statement and executes it.
Tip: See About Toad Script for more built-in functions that can be used in scripts that you execute in Toad.
Edit SQL
The editor combines a powerful SQL and procedure editor into a single interface.
Notes:
- See Editor Shortcut Keys for more information about keyboard shortcuts used in the Editor.
- Toad has implemented a large script mode to improve performance when opening files larger than 2 MB. By default, when you open a large file, some features such as word wrap, syntax highlighting, and the Script Map are disabled in the Editor. When a large script loads, a message indicates that you are in large script mode in the right margin of the Editor. You can cancel large script mode by clearing the option in Tools | Options | Editor | General.
- To show the Script Map, Script Results or Group Execute panes for the current Editor window select Editor | Windows | <pane>.
To edit SQL
-
Select Tools | Edit
Note: You can query the worksheet from the Editor by entering a dollar sign ($) followed by a cell range to limit the results similar to the following statement:
Select * from (MyWorksheet$A1:C10)You can also query the entire worksheet by entering a dollar sign ($) after the worksheet name; however, this returns empty rows and columns in the worksheet.
-
Review the following to help enter SQL:
-
Click
to format the code.
- Right-click a block of code in the Editor to see additional actions available in the context menu. For example, you can turn a line into comment, uppercase or lowercase it, surround with snippet, set numbered bookmark, collapse or expand nodes, send to Query Builder and etc. You can also right-click an object (e.g. a table) and manage it as if in the Object Explorer.
- Hold Alt + left-click and drag to select vertical blocks of text in Editor.
-
Select the block of code in the Editor, and click
to validate the syntax.
Note: Toad undoes any changes to the object and data in the database after executing the script (notice that the Result Sets tab is empty). To modify the object and data, you need to execute the script.
- Click Fetch n rows to specify the number of rows to initially retrieve when executing the query. You can set a default value for this option in Tools | Options | Database | General. See General Database Options.
-
Click
to execute SQL on the current server or multiple servers at one time.
. The button will retain the function of the last execute action for the active Editor window. If you switch to another Editor window, the button will change to reflect the last action for that window.
- Select the block of code in the Editor and click
to create a stored procedure. Stored Procedure creation dialog automatically recognizes procedure body and declared parameters.
- Toad automatically saves a backup copy of any modified editor file in the Application Data Directory to avoid losing your work. When you restart Toad after an unexpected close, Toad checks the timestamp on the backup file against the original file, and opens the file that has the latest timestamp. The document recovery option is in Tools | Options | Environment | General.
- You can save the current connection, SQL script, executed results, filters, and group execute connections/results (if enabled) in a Toad editor file (.tef). This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.
- Editor supports floating window mode.
- The script is automatically checked for syntax errors when executing current statement (F9), checking syntax, changing database, or refreshing the Script Map. The syntax errors are listed in the Script Results pane in the Messages tab.
- If you copy and paste a SQL statement from one editor window to another, Toad automatically rewrites the statement to match the syntax of the connection for the second editor window.
Configure Editor Options
You can specify options for the look and behavior of the SQL Editor by using the Toad Options dialog.
To configure Editor options
- Select Tools | Options.
-
To specify one of the following options, select the corresponding page in the Options dialog.
Option Description
Page in Options Dialog
Display the tabs for the current connection only when using Tabbed windows
Select Environment | Interface
Change background color of the Editor Select Editor | General Use connection color as background color Select Editor | General Position Editor tab headers vertically Select Environment | Interface Set options to display line numbers, line modifications, and general text formatting
Select Editor | General
Specify options to automatically replace typed text with the correct substitution text
Select Editor | Auto-replace
Customize code formatting options including headers and layout characteristics
Select Editor | Formatter
