Toad Data Point provides native database connections to the following database providers:

• Oracle
• SQL Server
• DB2 (LUW and z/OS)
• Sybase ASE, Sybase IQ, and Sybase SQL Anywhere
• Teradata
• MySQL

Toad also allows you to create an ODBC connection to a database that supports an ODBC 3.0 (or later) driver. However, an ODBC connection may not be as full-featured as the native connection, so Toad recommends using the native connection whenever possible.

Toad Data Point provides two tools to let you view database objects:

• Object Explorer provides a list of database objects.
• Database Explorer allows you to view the details of database objects.

Toad Data Point provides two ways to create a query:

• Build queries visually using Query Builder
• Compose queries using the Editor

To Build Queries Visually

The Query Builder enables you to create a query without writing or editing SQL statements. Even if you are familiar with SQL, the graphical interface makes it easy to create relationships and visualize the query.

1. Select Tools | Query Builder | Query Builder.
2. Drag tables and views from the Object Explorer to the Diagram pane.

3. Join columns by selecting a column in a table and dragging it to a column in another table. A connector line displays between the two objects to visually represent the relationship.

   Note: By default, all joins are initially created as Inner Joins. Double-click to modify the join type.

4. Add columns to the query using one of the following methods:
   • Select each column you want to add to the query.
   • To add all columns, select (Add All Columns).
   • To add all columns to the query using a SELECT * statement, select * (Wildcard) . If selected, an asterisk displays for the Field name in the Criteria tab.

   The selected columns are displayed in the bottom portion of the Diagram window.

   

5. To select the type of statement you want to create, click (the default is the Select statement).

   Notes:

   • The CREATE statement is only available for Oracle, DB2, SQL Server, and MySQL.
   • If you select an INSERT, UPDATE, or DELETE statement after adding a single table or view to the Query Builder, you cannot add another table or view because it can only apply to a single table or view. Also, if you add more than one table or view to the Query Builder, this action is disabled because it only applies to a SELECT or CREATE statement.
   • You can click to remove a column from the query.

6. Complete the query options. Review the following for additional information:

   Query Builder Options	Description
   Only fetch unique records	Select this checkbox to eliminate duplicate records from query results.
   Top row count	Enter the number of records that you want to return that have the highest values. You can also select the Percent checkbox to select a percentage of records to return instead of an exact value. Note: (For Oracle only) If you select a top row count and attempt to reverse engineer the query, the resulting query does not populate this field. Instead, the original query is placed into a subquery and the workaround wrapping SELECT statement is placed in the main Diagram page. The WHERE ROWNUM condition is placed into the Global Where clause.
   Aggregate Function	Select one of the following functions to calculate column values: Avg—Returns the average of the values in a group. Avg (Distinct)—Returns the average of the unique values in a group. Count—Returns the total number of items in a column. This function does not ignore NULL values when calculating results. Count (Distinct)—Returns the total number of unique values in a column. Max—Returns the maximum value for the column. Min—Returns the minimum value for the column. Stddev—Returns the sample standard deviation of a numeric expression evaluated over a set. Sum—Returns the sum of all the values in the expression. Sum (Distinct)—Returns the sum of all the unique values in the expression. Variance—Returns a statistical measure of how far a set of numbers are spread out from each other. Note: If you add a Group By clause, you must specify an aggregate function for any remaining columns.
   Where Condition	Use to compose a Where clause or to add a subquery. Select the Where field in the column and click . In the Where Condition editor, then do one of the following: Use the Where Condition editor to compose the clause. See Set Where Conditions in the online Help. Click Subquery in the editor to add a subquery. See Add Subqueries in the online Help. To learn about adding date ranges, see Set Date Ranges in the online Help. Notes: If you reverse engineer a query that contains a Where condition from the Editor or edit it in the Query tab of the Query Builder, it displays in a Global Where clause bubble on the Diagram pane rather than the Where Condition field below the Diagram pane. You can double-click the bubble to edit the clause. You can also use the Where Condition to add the same column twice to the query.
   Or	Select the operators and expressions to add to the OR condition.
   Group By	Select the column you want to add the Group By clause to and click +. You can add a new Group By clause to any remaining columns to group them in sequence. Any remaining columns that do not have a Group By clause must include an aggregate function. Tip: You can add a Group By clause to all selected columns in each table/view, by right-clicking the Diagram pane and selecting Add Group By.
   Having Condition	Select the operators, aggregate, and expressions to include in the Having condition. This option is disabled unless you have set a Group By clause. Note: If you reverse engineer a query that contains a Having condition from the Editor or edit it in the Query tab of the Query Builder, it displays in a Global Having clause bubble (click here to see an example) on the Diagram pane rather than the Having Condition field below the Diagram pane. You can double-click the Global Having clause to edit it.
   Sort	Select an option to add this column to the Order By clause and specify a sort direction.
   Visible	Select this checkbox to return this column in query results. This is useful if you need to include a column in the selection criteria, but do not need to display it in the query results.
   Field Alias	Enter a name to use as an alias for the column name in the query results. This is useful if you have an ID or vague column name and want to easily identify that column in the query results.
   Table Alias	Enter a name to use as an alias for the table name in the query results. For example, if there are multiple employee tables that you need to join for the query, you can rename the tables to permanent, contract, etc., to easily identify them. Note: If you selected All Columns for a table or created a column using the Edit Calculated Fields window, you cannot modify the table alias.

7. Click to execute the query.
8. To save the query, right-click the Query Builder tab and select Save File. If you save the file as a Query Builder file (.tsm), Toad saves the current connection with the file.

Notes:

• To create a cross-connection query, click the arrow beside and select Cross-Connection Query Builder.
• To view details for a table in the Diagram, press F4. If a table is not selected, details for the last selected table display.

• To create a data report, pivot grid, or chart from the data, right-click the data and select Send To | report_type.

  

To Use the Editor to Compose Queries

Toad Data Point includes a full-featured Editor for composing SQL statements.

1. To open an Editor window, To open an Editor window, Select Tools | Edit | SQL Editor.
2. Type your statement text.

3. To invoke the code completion feature, press PERIOD at the location where you want to display a list of objects.

   Tip: You can specify the default options for using code completion in Tools | Options | Editor | Code Completion.

4. To format the code, Select Editor | Format Code.
5. To validate the syntax, highlight the block of code in the editor and select Editor | Check Syntax.
6. To execute the SQL statement, click .
7. To save the query, right-click the Editor tab and select Save File. If you save the file as a Toad Editor file (.tef), Toad saves the current connection with the file.

Note: To create a cross-connection query using the Editor, Select Tools | Edit | Cross-Connection SQL Editor.

Toad Data Point provides an Automation tool that allows you to quickly create automation scripts that perform querying and reporting tasks, as well as a number of other activities. You can schedule querying tasks to run when database activity is light, save the results as Excel spreadsheets or Toad Reports, and then distribute the reports automatically.

To Create an Automation Script

1. Select Tools | Automation.
2. Before building your script, click Settings in the script design window and specify the script settings. Use the Activity details pane in the bottom portion of the Automation window to enter your settings. Review the following for additional information:

   Activity Input
   Stop or continue on Error	Select Stop On Error to stop the script if an error occurs.
   Send email when error occurs	Select this option to send an email on script error. If selected, you must also click Compose Email to compose the email message and to edit the Email Server Settings. Note: When you specify the error email settings, Toad saves your settings and uses them in all subsequent automation scripts.
   Use relative path to find SQL files	Select to use a relative path that does not include the drive or full path for all activities in the script. This is useful if you want to share your automation scripts with colleagues, use them with version control, or place them on a shared network folder.
   Embed SQL into Automation script	Select to embed the actual SQL from SQL files in the script. This makes it easier to share the script with colleagues.
   Truncate log	Select to overwrite the log file each time the script runs.
   Run Mode	Select Test mode to run your script in test mode using the database connection specified under Test settings. Select Production mode to run your script in production mode using the database connection specified under Production settings. Note: The default connection is the currently active, open connection.
   Activity Info
   Name	In the Activity Info window, enter a script name.

3. To build your script, click an activity in one of the Activity groups in the Toolbox, or drag an activity from the Toolbox to the Automation script design window. Repeat this process to add additional activities to your script.

   Note: You must drag the activity to . When you see the Activity icon replace , then release the mouse button.

4. Select each activity in the design window and then specify the activity's properties in the tabbed Activity widows.

   

   Tip: You can save an activity and its properties as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

   Note: Click Settings in the design window at any time to go back and review the Automation script settings.

5. After you finish building your script, select one of the following actions from the Wizard bar:

   Click this...	To do this...
   Add to Project	Add an automation script to the current project in the Project Manager. The script is automatically placed in the project's Automation Scripts folder.
   Save	Save an automation script (.tas) in a folder on a local or network drive.
   Run	Test an automation script before scheduling it. The script executes once and the execution log is displayed in the Log window. Resolve any problems that cause activities to fail or disable failed activities before scheduling the script. Tips: Press F5 to run the automation script. To view the script log, select the Log tab or select View | Output.
   Schedule	Schedule an automation script in the Job Manager. You can schedule a script to execute once or to run repeatedly at a specific interval.