Chatta subito con l'assistenza
Chat con il supporto

SQL Navigator for Oracle 8.0 - 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

SQL Modeler

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.

Toolbar

Icon Tool tip Description
New model Clear the modeler window ready to create a new query.
Open an existing model Open a saved query.
Save current model as... Save the query. Specify the filename and location.
Save model Save the query to the current filename and location.
Model information Edit current model information
Edit calculated fields Edit calculated fields

Generate a SELECT statement

Select the type of statement you want to create.

If the query in the SQL Modeler is an UPDATE, DELETE or INSERT statement, a rollback will occur automatically.

Execute query Use to test the query. The results show in Query Results Tab.
Explain plan Open the Explain Plan Tool.
Load in the Code Editor Copy the new query to the Code Editor.

Show Table List

Show/Hide the Table Selector.

The Table Selector lists the tables, views and synonyms available to you for inclusion in your SQL Model. You can select from the current schema or any other schema. Only those tables, views or synonyms for which you have SELECT privilege will be listed.

To add a table, view or synonym, double click on it or drag and drop it onto the Model Area.

  Save sub query and return to master query Save sub query and return to master query
  Cancel sub query and return to master query Cancel sub query and return to master query

Edit global where clauses

Edit global WHERE clauses.

Open the Global WHERE Conditions Window.

Edit global having clauses

Set Global HAVING conditions (Must have a Group By condition set in the Criteria Tab.)

Open the Global HAVING Conditions Window.

View joins View Joins Dialog
Auto join objects Auto join objects
  Percentage of zoom for modeler pane

View Joins Dialog

From this dialog you can see individual joins, browse through the joins, and make changes to the joins.

Dialog Section Description
From / To The join fields, joined from one table to another.
Join Type You can change the Join Type from Inner to Outer. The line color denotes the type of join.
Outer Join On If you have selected an Outer join, you can change which table the outer join is performed on.

Join Test

You can change the test for the join.

You can make it Less than, Greater than, etc. instead of Equal to.

Buttons
Previous Join Delete the current join.
Next join Move forward in the join list.
Delete Join Move backward.
OK Close the window and return to the SQL Modeler.

To open this dialog, click View Joins on the SQL Modeler toolbar. Alternatively, double click on a join line in the Model Area.

Global WHERE Conditions Window

Add, Edit, Delete global where conditions as per the toolbar.

The Add and Edit buttons open the Global WHERE Definition dialog. Global Where entries are in the form of <expression1> <operator> <expression2>.

TIP: You could alternatively populate the WHERE clause via the Criteria Tab. Entries into the cells of the Criteria tab should be in the form of <operator> <expression2>.

 

Example

Construct the following query

SELECT dept.deptno, dept.dname, dept.loc

FROM scott.dept

WHERE ((dept.deptno BETWEEN 1 AND 25)

AND (dept.deptno < 40)

AND (dept.loc IS NOT NULL)

Follow the numbered steps

  1. Open the SQL Modeler (as SCOTT/TIGER).
  2. Double-click DEPT to add it to the model.
  3. Right-click DEPT and choose Select All.
  4. On the criteria tab, double click the Where Cond. cell under DEPTNO.
  5. Choose < and fill in the value 40. Click OK.
  6. On the criteria tab, double click the Where Cond. cell under LOC and then choose the Expert tab and click Yes at the warning dialog.
  7. In the top edit box, enter IS NULL. Click OK.
  8. On the criteria tab, double-click the OR cell under LOC. Choose the Expert tab and click Yes at the warning dialog.
  9. In the top edit box, enter = 'CHICAGO'
  10. In the table model area (the area around the table images), right-click and choose SQL>Global Where
  11. In the top edit box, enter Data Field DEPT.DEPTNO. Click OK.
  12. From the SQL Function panel, select BETWEEN _Const_ AND _Const_. Replace the constants with values, for example 1 and 25 respectively. Click OK twice.
  13. View the generated query. It should appear as described above.
Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione