Chat now with support
Chat with Support

SQL Navigator for Oracle 7.6 - User Guide

Quick Overview Working With SQL Navigator Navigation Oracle Logon Code Editor Visual Object Editors Team Coding and Version Control Systems Modules, Windows And Dialogs
Analyze Tool Auto Describe Tool Benchmark Factory Bookmarks Dialog Capture Web Output Change Logon Password Dialog Code Analysis Code Assistant Code Road Map Code Templates Code Test Database Source Code Search Dialog DB Navigator Describe Difference Viewer Edit Data ER Diagram Explain Plan Tool Export Table Find and Replace Dialog Find objects Dialog Find Recycle Bin Objects Dialog Formatting Options HTML Viewer Import HTML as PL/SQL Import Table Java Manager Job Scheduler Locate In Tree Output Window PL/SQL Profiler Profile Manager Project Manager Publish Java to PL/SQL Quick Browse Rename Object Search Knowledge Xpert Select DB Object Dialog Server Output Server Side Installation Wizard Session Browser Source Preview SQL Modeler SQL Optimizer Task Manager Web Support Configuration Dialog Wrap Code
View | Preferences About Us

Global HAVING Coditions 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

  1. Open the SQL Modeler (as SCOTT/TIGER).
  2. Double-click EMP to add it to the model.
  3. Right-click EMP and choose Select All, then deselect Hiredate.
  4. In the Criteria tab, double-click the Group By field for DEPTNO.
  5. Double-click the Group By fields for COMM, SAL, MGR, JOB, ENAME and EMPNO as well.
  6. Click the Global Having button in the toolbar. Click the Add button to add a new Having clause.
  7. Enter the Having clause to say:
    • EMP.SAL + NVL(EMP.COMM, 0) > 4000
  8. 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.

Model Area

Use the model area to visually join or manipulate the Tables, Views and Synonyms.

 

Add objects

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:

  • To open SQL Modeler with a table, select the table in DB Navigator then click Object Menu | SQL Modeler.
  • You can click in a table header and drag and drop the table where you want it in the Model Area.

 

Build a query

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.

Note: If no table columns are selected, then all columns will be included in the query.

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.

 

Create a table join

  1. Add two table names to the Model Area.
  2. In the first table, click the column name that you want to join. Drag it to the corresponding column name in the second table.
  3. When the join is successfully created, SQL Modeler draws a connecting line that represents the join between the two table columns.

TIP: To specify criteria for a table join, double-click on the connecting line joining the two columns. This opens the View Joins Dialog.

 

Menus in the Model Area

Right click the model

Menu Description

Copy Model Image To Clipboard

Copy a bitmap image of the model to the Windows Clipboard

Tables

Visibility

Show / Hide tables in the model.

Hidden tables are not included in the Generated Query (Generated Query Tab).

Calculated Fields Add calculated fields based on other table columns.

Show
Join Text Show the column names that comprise the joins.
Adjust Model Origin Move the model so the upper left object is in the upper left of the Model Area.
Primary Key Show the Table Primary Keys. Show PK next to each Primary Key column.
Indexes Show the Table Indexes. Show IDX next to each Index column.
Show Field Type Show the column data type in each table.

SQL
Run Query in Thread Run the query in a way that allows you to halt it in the middle if necessary.
Global Where Clauses Open the Global WHERE Conditions Window. Use to add a WHERE clause to the query.

Global Having Clauses

Open the Global HAVING Conditions Window. Use to add a HAVING clause to the query.

TIP: Use the Criteria Tab to set GROUP BY first.

Query Variables Add variables. These variables are used in the Global WHERE Definition Dialog and Global HAVING Definition Dialog.
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.

Right click the Table object

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).

TIP: To hide multiple tables or show hidden tables, right click on the Model Area and select Tables | Visibility.

Keyboard Shortcuts

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)

Tabs

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.

  • There is a horizontal splitter between the Model Area and the Criteria/Generated Query/Query Results tabs. Drag it up or down and release to see more or less detail.
  • There is a vertical splitter between the Model Area and the Table Selector List. Drag it left or right to see more or less detail.

Criteria

  1. Add tables, views and synonyms to the Model Area.
  2. In these tables, views and synonyms, select the columns you want to add to the Criteria tab. Only Selected columns appear on the Criteria tab.

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.

Section Description
Conditions Set a condition which tests if a column is equal to (or <, >, <=, >=, <>, LIKE, BETWEEN) a constant or another field _or_ a condition where the selected field is in a sub query.
Clear Form Click to reset the dialog box and begin the WHERE condition again.
Remove Condition Click to remove the WHERE definition.

 

TIP: A quick way to remove the WHERE definition is to highlight the cell and press Delete.

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.

Note: Requires a value in the Group By cell. To create more complex Having conditions see Global HAVING Conditions Window for more information.

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.

 

Right-click over the Criteria grid:

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

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating