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.