Manage View

This window is used for creating, displaying and altering views (and summary tables). It is one of the forms of the Manage Object window. That section has general information about this window, including the use of the buttons and menu items.

This window is part of the Administration Component of AQT.

You can activate this window in the following ways:

• from the Database Explorer, select a View, then Create, Properties of, or Alter
• from the Run SQL window, click on Create > View.
• from the Manage Table window, click on New from the View tab.

The window has three parts, described in sections below:

• general properties of the view
• view text
• view columns

General Properties of the View

Shows the miscellaneous properties of the view, such as Description and Check Option. The properties you see will differ depending on your database type.

When creating or altering a view, you will also see a checkbox Include Column List. This specifies whether the column list is to be included in your view definition. This is discussed under View Columns.

View Text

Displays the query that your view is based on, which will vary depending on where Manage View was started:

• if you started Manage View from the Database Explorer, and had a table selected, the view text will be select * from tablename.
• if you started Manage View from the Run SQL window, your query text will be used as the view text
• if you started Manage View from the Manage Table window, the view text will be select * from tablename

You can amend this view text.

View Columns

View Columns are the names of the columns in your view. By specifying these, your view can have different column names than the column names in your query.

When creating or altering a view:

• when you check Include Column List, the view columns will be specifed in your view definition. This gives you the ability to have view columns which are different from your query columns.
• if you do not have Include Column List the view columns will not be specified in your query; your view columns will be the same as your query columns. If your query returns columns of the same name, or with no name, your database may give you an error (different database have different rules here). The circumvention to this is to specify a column list.
• clicking on Load from View Text will set the view columns to the names of your query columns. This is often a good starting point for setting the names of your view columns. You should always run Load from View Text after changing your query so that it returns different columns.
• Load from View Text will run your query (from a technical point of view, it just does a Prepare). If there is a syntax error in your view query, you will get an error message and the view columns will not be loaded.

Warning - Altering a View

When you alter a View, AQT will do this by dropping the existing view then creating it with your new definition. If the Create fails, you will no longer have the view in your database.

In this case, do not exit this window or hit Refresh - otherwise the view will be gone forever.

You must correct the definition so that the view is created successfully. If you cannot do this, do a Preview and save the Create statement in a file to correct at a later time.

This warning does not apply to Oracle (for which AQT uses Create or Replace View) or SQL Server (for which AQT uses Alter View).

Summary Tables

This window is also used for managing DB2/UDB Summary Tables. A Summary Table, also called a Materialised Query Table, is a table which is based on the result of a query. It is much like a view, except that the data actually exists (unlike a view, which is just a definition).

Defining a Summary Table is very similar to defining a view, so much of the discussion above still applies.