Chat now with support
Chat with Support

Toad Data Point 4.2 - User Guide

Tutorial: 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

  1. Connect to the Toad Sample (Access) database in the Navigation Manager.

  2. Select Tools | Query Builder | Query Builder.
  3. Drag the ADDRESS table to the Diagram pane.
  4. Click these column names in the ADDRESS table to add the columns to the query:

    • ADDRESS_ID
    • REGION_ID
  5. To add a subquery:
    1. Select the Where field in the REGION_ID column and click . The Where Condition editor displays.

    2. Select the IN operator from the list, and then click Subquery.

    3. 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.

    4. Drag the REGION table to the Diagram pane and select the REGION_ID column.

    5. Select the Where field in the REGION_ID column and click . In the Where Condition editor, select the In comparison operator, select Constant, and select the values 1 and 2.

    6. Your subquery window should look something like this:

    7. Click to execute the subquery. Select the Results tab to review the result set for the subquery.
  6. 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.

  7. Click to execute the main query.

 

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

  1. Select Tools | Edit | SQL Editor.

    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.

  2. Review the following to help enter SQL:

    • Code completion
    • Script Map
    • Code regions
    • Code snippets
    • Toad views
    • SQL recall
    • Add/remove application code
    • View Objects at Cursor
    • Macros to record keystrokes
  3. Click to format the code.

  4. 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.
  5. Hold Alt + left-click and drag to select vertical blocks of text in Editor.
  6. 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.

  7. Click to execute SQL on the current server or multiple servers at one time.  In the Editor toolbar, the execute SQL buttons are grouped under . 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.

  8. 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.

Tips:

  • 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

  1. Select Tools | Options.
  2. 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

Click here to view a video of this feature.

Editor Shortcut Keys

You can assign individual shortcut keys in Keyboard options. You can also customize Toad to use the shortcut keys of existing applications such as Toad for Oracle, SQL Navigator, or Query Analyzer in the Configuration Wizard (Tools | Configuration Wizard).

Caution! If you have some global access keys assigned, make sure that Toad shortcut keys do not coincide with them. When using such duplicating shortcut keys in Toad, the third party application commands, assigned globally, will be performed instead of ones assigned by Toad.

The following table describes the default shortcut keys in Toad.

Frequently Used Functions

Description

CTRL+PERIOD

Displays the code completion list

F9

Execute current statement

F5

Execute all statements, in order, one at a time

CTRL+MINUS

Comments the selected line

CTRL+SHIFT+MINUS

Uncomments the selected line

CTRL+J

Collapses all code regions

CTRL+M

Expands all code regions

ALT+PAUSE

Stops script execution

   

Bookmark

Description

CTRL+SHIFT+0-9

Sets a numbered bookmark at the selected line

CTRL+0-9

Goes to the numbered bookmark

Case

Description

CTRL+L

Converts the selected text to lowercase

CTRL+U

Converts the selected text to uppercase

Clipboard

Description

CTRL+C

Copies the selected text to the clipboard

CTRL+X

Cuts the selected text for pasting in the clipboard

CTRL+V

Pastes text from the clipboard

Code Completion

Description

CTRL+PERIOD

Displays code completion list

ENTER

Selects an item and close the code completion list

CTRL+SPACE

Selects an item without closing the code completion list

CTRL+<right arrow>

Expands a node in the code completion list

CTRL+<left arrow>

Collapses a node in the code completion list

Comment

Description

CTRL+MINUS

Comments the selected line

CTRL+SHIFT+MINUS

Uncomments the selected line

Cursor

Description

HOME

Moves the cursor to the beginning of the line

CTRL+HOME

Moves the cursor to the beginning of the file

END

Moves the cursor to the end of the line

CTRL+END

Moves the cursor to the end of the file

Debugger

Description

SHIFT+F12

Step from cursor

F7

Step into

SHIFT+F7

Step out

F8

Step over

F11

Run to breakpoint

F12

Run to cursor

CTRL+B

Set breakpoint

CTRL+W

Add watch at cursor

Delete

Description

BACKSPACE

Deletes the selected text (if text is selected); otherwise, deletes the previous character

CTRL+BACKSPACE

Deletes text to the beginning of the previous word

CTRL+SHIFT+DELETE

Deletes the entire script

Find and Replace

Description

CTRL+F

Finds text

F3

Finds the next occurrence of text

SHIFT+F3

Finds the previous occurrence of text

CTRL+G

Goes to line number

CTRL+I

Performs an incremental search

CTRL+SHIFT+I

Performs a reverse incremental search

Indent

Description

TAB

Indents the selected line

SHIFT+TAB

Outdents the selected line

Macros

Description

CTRL+SHIFT+R

Starts/Stops recording a macro

CTRL+SHIFT+P

Plays the recorded macro

Miscellaneous

Description

F4

Invokes objects search

F4+SHIFT

Describes the object next to the cursor

F6

Toggles between the Editor and Data Grid

CTRL+R

Show/hide Results pane

CTRL+D

Sends SQL to Query Builder

SHIFT+F8

Recalls the previous SQL statement

CTRL+á

Scrolls up the window.

CTRL+â

Scrolls down the window

<auto-replace token>+SPACE

Automatically replaces a misspelled word, inserts symbols, and expands text.

Note: You can set autoreplace in Tools | Options | Editor | Autoreplace.

CTRL+SPACE

Displays the Code Snippets folder browser

<code snippet shortcut name>+CTRL+SPACE

Inserts the code snippet template

CTRL+DELETE

Sets the column value for the current cell to Null in the data grid

View Result Sets

The Result Sets tab displays a data grid for executed SQL statements and scripts that return data.

If you occasionally closed the Results pane, you can restore it by selecting Editor | Windows | Script Results.

Visual Inspection Grid

You can easily review all (or the selected Result sets) in one Result Sets tab. Left-click and hold Shift or Ctrl to select more than one Result sets of your script. Select All results to review all in one tab.

To enable view of all Result Sets in one Tab

» Check Options | Database | Script results | Enable all results view

Troubleshoot Data

  • If you cannot edit data, click the red icon in the lower left corner of the grid, and then click OK on the window that displays. You do not need to edit fields in this window.

  • By default, auto commit is enabled and any row changes you make are automatically submitted to the database. You can disable this option in Tools | Options | Environment | Grid.

Tips for Working with Data

To...

Do this...

Pin a result set so it is not overwritten by a subsequent query in the Editor or Query Builder.

Click the push pin beside the result set:

Set the value of a cell to null.

Press CTRL+DELETE.

Toggle between the Editor and Data Grid.

Press F6.

Add column or group summary values

Right-click the data and select Show | Summary Footer Panel. Then, right-click the Summary Footer Panel below the column you want to add a summary total to and select an option.

If you have grouped columns using the Grouping panel (Show | Group Panel), you can also add a summary total to grouped records.

Apply a summary function to the data (Sum, Min, Max, Count, Avg).

Right-click below the column you want to apply the summary function (but above the Navigation toolbar) and select an option.

Show or hide columns of data.

Right-click a column heading in the data grid and select Column Chooser.

Display data in a report, pivot grid, or chart.

Right-click the data and select Send To |<option>.

Compare differences between two data sets.

Right-click the data and select Compare To.

Display the data in a grid or card view; grid view is useful for viewing summary information at a glance; card view is useful for viewing information in greater detail.

Right-click a record and select Show |Grid View or Card View.

View multi-line text in the data grid as one-line text separated by spaces.

Right-click the data and select Show | Multi-line Text as One-line. When the option is cleared, only the first line of the multi-line text is shown. To see the full text hover a mouse over a data grid or expand it.
Related Documents