Toad for Oracle 15.0 - Getting Started Guide

How to use the Model Area

Note: If no table columns are selected, then all columns will be included in the query.

Build a Query

Follow this procedure to get started using the Query Builder.

To build a query

1. Drag-and-drop Tables, Views, or Synonyms from the Schema Browser, Project Manager, Object Palette, or the Object Search window to the Query Builder workspace tab.

2. Click in the checkbox by a column to add it or remove it from the active query's SELECT clause.

3. Drag-and-drop columns from one table to another to create joins between the tables.
4. Add any WHERE, GROUP BY, HAVING, or ORDER BY clauses by right-clicking on the column and selecting Include in n clause. Right-click and adjust properties for clauses where necessary.

5. To create a new clause, drag-and-drop columns from a table to WHERE, GROUP BY, HAVING or ORDER BY in the Query Browser tree node.

6. Click on the toolbar to save the model to disk.
7. Click the Generated Query tab to view or edit the generated query.
   1. If you update the diagram and the SQL code is not synchronized, click in the Generated Query toolbar to update the code.
   2. If you update the code, click in the Generated Query toolbar to updated the diagram.

See also Create a Subquery

Create a Sub-Query

You can easily create a subquery or nested subquery. Subqueries can be created from the FROM clause or the WHERE clause.

Columns must be dragged directly from the table area to be placed in subqueries, or from the current statement.

After initiating the subquery using one of the methods below, create the subquery as you would a normal query.

To create a subquery in the WHERE clause

1. Drag a column into the WHERE node.

2. In the Condition dialog, use the simple or complex mode.

   If you click Nested Subquery, the new subquery is created in the workspace.

To create an EXISTS subquery

1. Right-click the WHERE clause in the tree view.
2. Select Add EXISTS Subquery or Add NOT EXISTS Subquery.

To create a subquery in the FROM node

1. Right-click the FROM clause in the tree view.
2. Select Add | New Named Subquery (or New Inline View).

Populate the Where Clause

There are two ways to populate the WHERE clause in SQL generated by the Query Builder: as an individual WHERE, or as a global WHERE.

To populate the where clause

1. Do one of the following:
   • Right-click on the column under the SELECT node and select Include in Where Clause.
   • Drag a column from the select node to the WHERE node.

   Add conditions and select or clear any outer joins you want to apply.

   Note: To build a more advanced query, click the Expert tab and enter your code by typing it in the top box or double clicking on functions and data fields to enter them.

2. Click OK.

   Repeat until all conditions are added.

   Note: When you add multiple columns to a WHERE clause, they are automatically placed

3. If a condition should be an OR condition, rather than an AND, right-click on it and select OR.

To create a global WHERE clause

1. Right-click in the Table Model area.
2. Select SQL | Global Where Clauses.
3. Click .
4. Enter or build your condition.
5. Click OK to close the definition window.
6. Click OK.

Example

To construct the following query:

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC

FROM DEPT

WHERE (((UPPER (RTRIM (DNAME)) = 'SALES') AND (DEPT.DEPTNO < 40)) AND ((DEPT.LOC = 'CHICAGO')OR ((DEPT.LOC IS NULL = '')))

Do the following:

1. Open the Query Builder.
2. In the Object Palette, select the Scott schema and double-click the DEPT table to add it to the model.
3. Right-click DEPT and choose Select All.
4. Drag the DEPTNO column to the WHERE node.
5. Select < in the operator box, click Constant, and enter 40 in the condition box.
6. Click OK.
7. Drag the LOC column to the WHERE node.
8. In the WHERE definition dialog, click the Expert tab. Click OK to confirm.
9. Double-click IS NULL in the SQL Operators area and then click OK.
10. Drag the DEPTNO above LOC in the tree view.
11. Right-click the LOC column and select OR.
12. Right-click on OR | LOC and select Properties. Select = in the operator box, click Constant, and enter CHICAGO in the condition box.
13. Click OK.
14. In the table model area (the area around the table images), right-click and choose SQL | Global Where. Click .
15. In the top edit box, enter (UPPER (RTRIM (DNAME))) = 'SALES'.
16. Click OK and then click OK again.

View the generated query. It should display as described above.