Chapter 11 - View Functions

“It is easier to go down a hill than up it, but the view is much better at the top.”

-Arnold Bennett

Creating a Simple View

The purposes of views are to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used).

The Semantic Layer

A customer often has what is called

a semantic layer between the data

tables and the end users. A simple

definition of a semantic layer is a

view layer that uses business

terminology and does presentation.

The "semantic layer" may change column names, can even derive new column values, perform all aggregation, or whatever else the presentation layer needs to support the users.

Basic Rules for Views

Above are the basic rules of Views with excellent examples.

How to Modify a View

The REPLACE Keyword will allow a user to change a view.

Exceptions to the ORDER BY Rule inside a View

There are EXCEPTIONS to the ORDER BY rule. The TOP command allows a view to work with an ORDER BY inside. ANSI OLAP statements also work inside a View.

How to Get HELP with a View

The Help View command does little but show you the columns.

Views sometimes CREATED for Formatting or Row Security

Views are designed to do many things. In the example above, this view formats and derives data, limits columns, and also limits the rows coming back with a WHERE.

CREATING Views for Complex SQL such as Joins

A huge reason for Views other than security is to also make Complex SQL easy for users. This view already has the Inner Join built into it, but users just SELECT.

WHY certain columns need Aliasing in a View

When you CREATE a view, you have to ALIAS any aggregation or derived data (such as math). Why? So you can SELECT it later, without having to do a SELECT *. Here, we only chose two columns and used their ALIAS to retrieve them.

Aggregates on View Aggregates

The examples above show how we put a SUM on the aggregate Order_Sum.

Locking Row for Access

Most views utilize the Locking row for ACCESS command. This is because they want to be able to read while a table is being updated and loaded into. If the user knows a dirty read won’t have a huge effect on their job, why not make a view lock with an ACCESS Lock, thus preventing unnecessary waiting?

Updating Data in a Table through a View

You can UPDATE a table through a View if you have the RIGHTS to do so.

Maintenance Restrictions on a Table through a View

There are a few restrictions that disallow maintenance activity on a view with an INSERT, UPDATE or DELETE request. A view cannot be used for maintenance if it:

1. Performs a join operation – more than one table

2. Selects the same column twice – wouldn’t know which one to use

3. Derives data – because it does not undo the math or calculation

4. Performs aggregation – because this eliminates detail data

5. Uses OLAP functions – because OLAP data is calculated

6. Uses a DISTINCT or GROUP BY – eliminates duplicate rows

Perform maintenance on a table through a view, but see the restrictions above first.