Global HAVING Conditions Window
Add, Edit, Delete global having conditions as per the toolbar.
The Add and Edit buttons open the Global HAVING Definition dialog. Global Having entries are in the form of <expression1> <operator> <expression2>.
|
|
TIP: You could alternatively populate the HAVING clause via the Criteria Tab. First set a GROUPed BY field. Then set the Having clause for that field by entering it in the group cond. row. This has the limitation that you can only have the selected field on the left side of the relational operator. If you need to have multiple fields on that side of the operator, use the Global Having feature. |
Example
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))
Follow the numbered steps
- Open the SQL Modeler (as SCOTT/TIGER).
- Double-click EMP to add it to the model.
- Right-click EMP and choose Select All, then deselect Hiredate.
- In the Criteria tab, double-click the Group By field for DEPTNO.
- Double-click the Group By fields for COMM, SAL, MGR, JOB, ENAME and EMPNO as well.
- Click the Global Having button in the toolbar. Click the Add button to add a new Having clause.
- Enter the Having clause to say:
- EMP.SAL + NVL(EMP.COMM, 0) > 4000
- Click OK.
View the generated query.
It should appear 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.