Chat now with support
Chat with Support

Toad for Oracle 12.12 - Getting Started Guide

Table of Contents Welcome to Toad Toad Resources Required Privileges Create and Manage Database Connections Editor Basics Schema Browser Basics Data Grid Basics Work with Data Work with Database Objects Work with Code
Write Statements and Scripts Work with the Query Builder Execute Statements and Scripts Save Statements (SQL Recall) Work with PL/SQL Objects Debug Analyze Code Optimize SQL
Customize your Toad Environment Use Other Quest Software Products with Toad

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.

 

Populate the Having Clause

You can automatically populate the Having clause in the SQL generated by the Query Builder in one of two ways.

Note: To create a HAVING clause, you must have added columns to the GROUP BY node.

HAVING entries should be in the form of <expression1> <operator> <expression2>.

To populate the HAVING clause

  1. Do one of the following:
    • Right-click the desired column in the tree, and then select Include in Having Clause.
    • Drag a column from a table in the Table Model area to the HAVING clause.
  2. Enter or build the condition.
  3. Click OK.
  4. Repeat until complete.

Global HAVING clauses

In order to include a global HAVING clause, there must be a GROUP BY clause as well.

To create a global HAVING clause

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

Example

To construct the following query:

SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.sal,

emp.comm, emp.deptno

FROM emp

GROUP BY emp.deptno, emp.comm, emp.sal, emp.mgr, emp.job,

emp.ename, emp.empno

HAVING ((emp.sal + NVL (emp.comm, 0)> 4000))

Do the following:

  1. Open the Query Builder.
  2. In the Object Palette, select the Scott schema.
  3. Double-click the EMP table to add it to the model.
  4. Right-click EMP and choose Select All, then clear Hiredate.
  5. Drag DEPTNO, COMM, SAL, MGR, JOB, ENAME and EMPNO to the Group by node.
  6. Right-click in the Table model area and select SQL | Global Having. Click to add a new Global Having clause.

    Enter the Having clause to say:

    EMP.SAL + NVL(EMP.COMM, 0) > 4000

  7. Click OK twice.

View the generated query. It should display as described above. This query selects all the employees whose salary plus commission is greater than 4000.

The NVL command substitutes a null value in the specified column with the specified value, in this case, 0.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating