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. |
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
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.
Use the model area to visually join or manipulate the Tables, Views and Synonyms.
Show/Hide the Table Selector on the SQL Modeler Toolbar. It lists the tables, views and synonyms available to you for inclusion in your SQL Model. Double-click each desired Table, View, Synonym, OR drag and drop them from the list to the model area. As the objects are presented on the model area, join lines are drawn from any established foreign keys in the DDL.
Additionally, drag-and-drop objects from:
|
TIP:
|
Steps | Description | ||
---|---|---|---|
Clear an existing query from the Model Area, if required. | Click New Model on the SQL Modeler Toolbar. | ||
Define the type of statement you want to create. | Choose SELECT, UPDATE, DELETE, or INSERT from the SQL Modeler Toolbar. | ||
Add objects to the Model Area. |
Drag and drop Tables, Views, or Synonyms from the Table Selector to the modeling area. You can show/hide the Table Selector on the SQL Modeler Toolbar. | ||
Specify table columns to be used in the query. |
Select column names in the tables in the Model Area.
| ||
Set criteria for the query |
See the Criteria Tab. | ||
View your query as SQL code or as a data grid. |
Click the Generated Query Tab and Query Results Tab. | ||
Save the model |
Save the model from the SQL Modeler Toolbar. |
|
TIP: You can use the SQL Modeler Toolbar to copy the query to the Code Editor window. |
|
TIP: To specify criteria for a table join, double-click on the connecting line joining the two columns. This opens the View Joins Dialog. |
Menu | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Copy Model Image To Clipboard |
Copy a bitmap image of the model to the Windows Clipboard | ||||||||||
Tables |
| ||||||||||
Show |
| ||||||||||
SQL |
| ||||||||||
Auto Join All Objects | Join all tables based on DDL Foreign Key Constraints. | ||||||||||
Hide fields | Show / Hide the list of columns in the table in the model area. | ||||||||||
Zoom to table | Select a table to focus. | ||||||||||
Toggle Full Screen Mode | Show / Hide the Table Select List and Criteria/Generated Query/Query Results tabs. | ||||||||||
Optimize All Table Sizes | Minimize the size of the tables in the Model Area. | ||||||||||
Arrange Tables | Arrange the tables in the Model Area. |
Menu | Description | ||
---|---|---|---|
Set Table Alias | Set the Table Alias. The value is added to the Criteria Tab. | ||
Alias Field Names | Set the Field Alias for each column in the table. The values are added to the Criteria Tab. | ||
Auto Join | Automatically join this table to others based on DDL Foreign Key Constraints. | ||
Show Schema Name in SQL | Shows / Hide the schema name in the Generated Query (Generated Query Tab). | ||
Remove Table | Remove this table from the model. | ||
Select All | Select / unselect columns in the table. | ||
Unselect All | |||
Invert Selection | |||
Optimize Size | Restore the size of the table to its default size. | ||
Hide |
Temporarily hide the table from the model. While the table is hidden it will not be included in the Generated Query (Generated Query Tab).
|
Key | Action |
---|---|
Up and down arrow keys | Move you around in lists |
Space bar | Select / Unselect boxes |
Tab | Move forward one area (table, menu, list, etc) |
Shift-Tab | Move back one area (table, menu, list, etc) |
The SQL Modeler provides a fast means for creating the framework of a Select, Insert, Update, or Delete statement. You can select Tables, Views or Synonyms, join columns, select columns, and create the desired type of statement.
Section | Description |
---|---|
Model Area | Used to graphically lay out a query. |
SQL Modeler Toolbar | Most frequently used Modeler functions. |
Criteria Tab | Criteria used in generating the model. |
Generated Query Tab | Automatically generated SQL as a result of the model. |
SPLITTERS |
The SQL Modeler has two splitters to change how you divide the screen.
|
|
TIP: To rearrange the order of the columns on the Criteria tab, drag and drop them left or right. |
Field | Description | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Only fetch unique records |
Select to apply the DISTINCT command to the query. | ||||||||||
Schema |
The schema cannot be edited. | ||||||||||
Null Value Subst. |
Double click in the cell to enter a value to substitute for any null values. To clear the cell, highlight it and press Delete. | ||||||||||
Aggregate F. |
Double click in the cell to select an aggregate column function, such as Average, Count, Max, Min, or Sum. To clear the cell, highlight it and press Delete. | ||||||||||
Where Cond. |
Double-click in the cell to open the WHERE Definition dialog.
| ||||||||||
Or |
Double-click in the cell to open the WHERE Definition dialog (as above). This WHERE criteria will be OR'ed together with the above WHERE criteria. If you want to AND multiple column criteria together, then select Expert from the WHERE Definition dialog. For more information, see Global WHERE Conditions Window. | ||||||||||
Group By |
Double-click in the cell to select it as a GROUP BY column. A number in parentheses indicates the order of the columns in the GROUP BY clause. See also The Having Cond. cell. To clear the cell, highlight it and press Delete. | ||||||||||
Having Aggregate |
Double click in the cell to select an aggregate column function (such as Average, Count, Max, Min, or Sum) for the HAVING clause. This allows for the following type of query: SELECT emp.job_id FROM employee emp GROUP BY emp.job_id HAVING ((AVG (emp.salary) > 1500)) To clear the cell, highlight it and press Delete. | ||||||||||
Having Cond. |
Double click in the cell to open the GROUP BY Definition dialog.
To clear the cell, highlight it and press Delete. | ||||||||||
Sort |
Double click in the cell to sort this column as Ascending, Descending, or no sort. To clear the cell, highlight it and press Delete. | ||||||||||
Visible |
Double click in the cell to have it be visible / hidden. When visible the column is returned in the column list. | ||||||||||
Field Alias |
Double click in the cell to change the field name alias. | ||||||||||
Table Alias |
Double click in the cell to enter a value for the table alias. To clear the cell, highlight it and press Delete. |
Menu | Description |
---|---|
Suppress Current Column |
Remove this column from the query |
Best Fit (All Columns) |
Set the column width of the Criteria grid to show all text |
Default Width (All Columns) |
Set the column width of the Criteria grid to the default width |
Copy Query Grid Image to Clipboard |
Copy the Criteria grid to the clipboard |
© ALL RIGHTS RESERVED. 使用条款 隐私 Cookie Preference Center