Toad for Oracle 15.0 - Getting Started Guide

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.

Use the Generated Query

• Select a query and press CTRL+C
• Click the Send text to clipboard button
• Select the query, right-click and select Copy

Saving Toad Query Results

Any of Toad's window query results can be saved to the Windows Clipboard or to a file by the procedure below. Some dialog boxes do NOT have a Copy to Clipboard or Save to Disk function. This duplicates that functionality.

To save query results

1. Turn on spooled output to disk file: Database | Spool SQL | Spool SQL To File.
2. Run the desired Toad window (for example, the Schema Differences window) select each desired tab.
3. From the User Files folder, open DEBUG.SQL.
4. Copy each SQL into the Editor window.
5. Run each SQL in the Editor window, substituting hard coded values for the bind parameter variables, or just enter them when prompted in the Variables dialog box.
6. Save the grid contents to clipboard or disk file, using right-click, Export Dataset.

Execute Statements and Scripts

Toad provides an intuitive and efficient way to write, run, and test your SQL and PL/SQL code. Toad supports efficient code management for a single developer or a whole team of developers.