Views

View is a logical table based on one or more tables or views. A view contains no data itself, but retrieves it from underlying tables and views when requested.

dbForge Fusion allows you to create, modify, and drop views, retrieve and alter data in underlying tables.

Creating a View

To create a view:

  1. On the Devart - Standard toolbar, click the New Database Object button.
  2. In the New Object dialog window, select View.
  3. Give a unique name to a view.
  4. Click the Create button. If specified name is unique, the view editor will appear. Note that views share same namespace with tables.
  5. Type a query that will represent the view. You do not have to type “CREATE OR REPLACE VIEW … AS” because dbForge Fusion adds this clause for you.
  6. Optionally, you can alter the name, change owner in which the new view will be created, or set view comment.
  7. Optionally, you can switch to the Columns tab, and set column aliases and comments.
  8. Save the document. If there are any errors in the SQL syntax, you will be notified about it.

Editing a View

To edit a view:

  1. In Database Explorer window select a required view.
  2. Select the Edit View option from the shortcut menu.

In the opened document you can alter a query text. Note that after a view had been created, you can not modify its name and owner. Save the document to apply changes you made to the query text.

Dropping a View

To drop a view:

  1. In Database Explorer select a required view.
  2. Select the Delete option from the shortcut menu.

Retrieving and modifying view data

To retrieve view data, right-click on a required view in Database Explorer, and click Retrieve Data from the shortcut menu.

The data is rendered in grid-based editor. You can browse and edit view data in the same way as you browse and edit table data.

Example

Let’s create a view that represents a join between DEPT and EMP tables. The view must return columns “Department Name”, “Employee Name”, and “Job”; the data must be ordered in certain order. Let’s make the view not updatable. It may also be a good idea to force view creation so that it will not fail if some referenced objects are wrong. To construct this view you have to perform the following steps:

  1. In Database Explorer window, right-click the** Views** node, and select New View.
  2. Give a name to a view. Let it be TestView.

  3. Type the following SQL in the Query Text field:

     SELECT dname, ename, job FROM emp, dept WHERE emp.deptno = dept.deptno
  4. Switch to the Columns tab. In the Alias column, change default column names to the custom ones; that is, DNAME to Department Name, ENAME to Employee Name, and JOB to Job.
  5. Now switch to the Advanced tab, to tune up some view settings.
  6. Check the Force view creation option. This allows to create or modify a view even if some objects referenced by the view are missing or have NOT VALID status.
  7. Check the Restrictions check box to enable additional options for the view.
  8. Choose the Read Only option.
  9. Save the document.

You can invoke the text view of the document to inspect the query generated by dbForge Fusion.