Create Views

You can build views on other views and procedures that reference views. You can define primary, foreign, and common keys on views. However, you cannot associate rules, defaults, or triggers with views or build indexes on them. You cannot create temporary views, or views on temporary tables.

View names must be unique for each user among the already existing tables and views. If you have set quoted_identifier on, you can use a delimited identifier for the view.

select * 
from titles 
where price > $15 
  and advance > $5000 

Now, suppose you have a lot of retrieval and update operations to perform on this data. You can combine the conditions shown in the previous query with any command that you issue or you can create a view that displays only the records of interest:

create view hiprice 
as select * 
from titles 
where price > $15 
  and advance > $5000

When SAP ASE receives this command, it stores the select statement, which is the definition of the view hiprice, in the system table syscomments. Entries are also made in sysobjects and in syscolumns for each column included in the view.

Now, when you display or operate on hiprice, SAP ASE combines your statement with the stored definition of hiprice. For example, you can change all the prices in hiprice just as you can change any other table:

update hiprice 
set price = price * 2 

SAP ASE finds the view definition in the system tables and converts the update command into the statement:

update titles 
set price = price * 2 
where price > $15 
  and advance > $5000 

In other words, SAP ASE knows from the view definition that the data to be updated is in titles. It also increases the prices only in the rows that meet the conditions on the price and advance columns given in the view definition and those in the update statement.

Having issued the update to hiprice, you can see its effect either in the view or in the titles table. Conversely, if you had created the view and then issued the second update statement, which operates directly on the base table, the changed prices would also be visible through the view.

Updating a view’s underlying table in such a way that different rows qualify for the view affects the view. For example, suppose you increase the price of the book You Can Combat Computer Stress to $25.95. Since this book now meets the qualifying conditions in the view definition statement, it is considered part of the view.

However, if you alter the structure of a view’s underlying table by adding columns, the new columns do not appear in a view that is defined with a select * clause unless you drop and redefine the view. This is because the asterisk in the original view definition considers only the original columns.

• create view Syntax
  You need not specify any column names in the create clause of a view definition statement. SAP ASE gives the columns of the view the same names and datatypes as the columns referred to in the select list of the select statement.
• select Statement Usage with create view
  The select statement in the create view statement defines the view. You must have permission to select from any objects referenced in the select statement of a view you are creating.
• Validate a View’s Selection Criteria
  When you create a view using the with check option clause, each insert and update through the view is validated against the view’s selection criteria. All rows inserted or updated through the view must remain visible through the view, or the statement fails.