create view

create view [owner.]view_name
	[(column_name[, column_name]...)] 
	as 
	select [distinct] select_statement
	[with check option]

view_name

is the name of the view. The name cannot include the database name. If you have set quoted_identifier on, you can use a delimited identifier. Otherwise, the view name cannot be a variable and must conform to the rules for identifiers. For more information about valid view names, see “Identifiers” in Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” of Reference Manual: Building Blocks. Specify the owner’s name to create another view of the same name owned by a different user in the current database. The default value for owner is the current user.

column_name

specifies names to be used as headings for the columns in the view. If you have set quoted_identifier on, you can use a delimited identifier. Otherwise, the column name must conform to the rules for identifiers. For more information about valid column names, see “Identifiers” in Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” of Reference Manual: Building Blocks.

You can always supply column names, but they are required only:

• A column is derived from an arithmetic expression, function, string concatenation, or constant

• Two or more columns have the same name (usually because of a join)

• You want to give a column in a view a different name than the column from which it is derived (see Example 3)

Column names can also be assigned in the select statement (see Example 4). If no column names are specified, the view columns acquire the same names as the columns in the select statement.

select

begins the select statement that defines the view.

distinct

specifies that the view cannot contain duplicate rows.

select_statement

completes the select statement that defines the view. The select statement can use more than one table and other views.

with check option

indicates that all data modification statements are validated against the view selection criteria. All rows inserted or updated through the view must remain visible through the view.

create view titles_view 
as select title, type, price, pubdate 
from titles

Creates “new view” from “old view.” Both columns are renamed in the new view. All view and column names that include embedded blanks are enclosed in double quotation marks. Before creating the view, you must use set quoted_identifier on:

create view "new view" ("column 1", "column 2")
as select col1, col2 from "old view"

Example 3

Creates a view that contains the titles, advances, and amounts due for books with a price less than $5.00:

create view accounts (title, advance, amt_due) 
as select title, advance, price * total_sales 
from titles 
where price > $5

Example 4

Creates a view derived from two base tables, authors and publishers. The view contains the names and cities of authors who live in a city in which there is a publisher:

create view cities 
 (authorname, acity, publishername, pcity) 
as select au_lname, authors.city, pub_name,
publishers.city 
from authors, publishers 
where authors.city = publishers.city

Example 5

Creates a view with the same definition as in the previous example, but with column headings included in the select statement:

create view cities2 
as select authorname = au_lname,
acity = authors.city, publishername = pub_name, pcity = publishers.city 
from authors, publishers 
where authors.city = publishers.city

Example 6

Creates a view, author_codes, derived from titleauthor that lists the unique author identification codes:

create view author_codes
as select distinct au_id
from titleauthor

Example 7

Creates a view, price_list, derived from title that lists the unique book prices:

create view price_list (price)
as select distinct price
from titles

Example 8

Creates a view of the stores table that excludes information about stores outside of California. The with check option clause validates each inserted or updated row against the view’s selection criteria. Rows for which state has a value other than “CA” are rejected:

create view stores_cal
as select * from stores
where state = "CA"
with check option

Example 9

Creates a view, stores_cal30, which is derived from stores_cal. The new view inherits the check option from stores_cal. All rows inserted or updated through stores_cal30 must have a state value of “CA”. Because stores_cal30 has no with check option clause, you can insert or update rows through stores_cal30 for which payterms has a value other than “Net 30”:

create view stores_cal30
as select * from stores_cal
where payterms = "Net 30"

Example 10

Creates a view, stores_cal30_check, derived from stores_cal. The new view inherits the check option from stores_cal. It also has a with check option clause of its own. Each row that is inserted or updated through stores_cal30_check is validated against the selection criteria of both stores_cal and stores_cal30_check. Rows with a state value other than “CA” or a payterms value other than “Net 30” are rejected:

create view stores_cal30_check
as select * from stores_cal
where payterms = "Net 30"
with check option

Example 11

create view psych_titles as
   select *
      from (select * from titles
               where type = "psychology") dt_psych

Restrictions on views

• You can create a view only in the current database.

• The number of columns referenced by a view cannot exceed 1024.

• You cannot create a view on a temporary table.

• You cannot create a trigger or build an index on a view.

• You cannot use readtext or writetext on text or unitext, image columns in views.

• You cannot include order by, compute clauses or the keyword into in the select statements that define views.

• You cannot update, insert, or delete from a view with select statements that include the union operator.

• If you create a view using a local or a global variable, Adaptive Server issues error message 7351: “Local or global variables not allowed in view definition.”

• You can combine create view statements with other SQL statements in a single batch.

  WARNING! When a create view command occurs within an if...else block or a while loop, Adaptive Server creates the schema for the view before determining whether the condition is true. This may lead to errors if the view already exists. To avoid this, verify that a view with the same name does not already exist in the database or use an execute statement, as follows:

  if not exists
      (select * from sysobjects where name="mytable")
  begin
  execute "create table mytable (x int)"
  end
  

View resolution

• If you alter the structure of a view’s underlying tables by adding or deleting columns, the new columns do not appear in a view defined with a select * clause unless the view is dropped and redefined. The asterisk shorthand is interpreted and expanded when the view is first created.

• If a view depends on a table or view that has been dropped, Adaptive Server produces an error message when anyone tries to use the view. If a new table or view with the same name and schema is created to replace the one that has been dropped, the view again becomes usable.

• You can redefine a view without redefining other views that depend on it, unless the redefinition makes it impossible for Adaptive Server to translate any dependent views.

Modifying data through views

IDENTITY columns and views

group by clauses and views

• When creating a view for security reasons, be careful when using aggregate functions and the group by clause. A Transact-SQL extension allows you to name columns that do not appear in the group by clause. If you name a column that is not in the group by clause, Adaptive Server returns detailed data rows for the column. For example, this Transact- SQL extended column query returns a row for every 18 rows—more data than you might intend:

  select title_id, type, sum (total_sales)
  from titles
  group by type
  

  While this ANSI-compliant query returns one row for each type (6 rows):

  select type, sum (total_sales)
  from titles
  group by type
  

  For more information about group by, see “group by and having clauses.”

distinct clauses and views

• The distinct clause defines a view as a database object that contains no duplicate rows. A row is defined to be a duplicate of another row if all of its column values match the same column values in another row. Null values are considered to be duplicates of other null values.

  Querying a subset of a view’s columns can result in what appear to be duplicate rows. If you select a subset of columns, some of which contain the same values, the results appear to contain duplicate rows. However, the underlying rows in the view are still unique. Adaptive Server applies the distinct requirement to the view’s definition when it accesses the view for the first time (before it does any projection and selection) so that all the view’s rows are distinct from each other.

  You can specify distinct more than once in the view definition’s select statement to eliminate duplicate rows, as part of an aggregate function or a group by clause. For example:

  select distinct count (distinct title_id), price
  from titles
  

• The scope of the distinct applies only for that view; it does not cover any new views derived from the distinct view.

with check option clauses and views

Getting information about views

Creating views from SQL derived tables

The use of more than one distinct keyword and the use of “column_heading = column_name” in the select list are Transact-SQL extensions.

Permissions

create view permission defaults to the Database Owner, who can transfer it to other users.

Permissions on objects at view creation When you create a view, Adaptive Server makes no permission checks on objects, such as tables and views, that are referenced by the view. Therefore, you can create a view successfully even if you do not have access to its objects. All permission checks occur when a user invokes the view.

Permissions on objects at view execution When a view is invoked, permission checks on its objects depend on whether the view and all referenced objects are owned by the same user.

Commands create schema, drop view, select, update

System procedures sp_depends, sp_help, sp_helptext, sp_rename

---

Copyright © 2009. Sybase Inc. All rights reserved.

View this document as PDF