Creating Views Procedure
The following procedure shows how to create views using BTEQ:
1 Log on to Teradata Database as user DBADMIN using BTEQ.
2 Create one or more views using the CREATE VIEW statement. For example:
CREATE VIEW database_name.view_name (column_name, column_name) AS
SELECT clause;
where:
|
Syntax Element |
Explanation |
|
database_name |
The name of the database where the view will be created if it is different from the current database. Recommendation: The database should be a Views Database or a database located within a Views Database. See “About Database Creation” on page 53. If database_name is not specified, the system creates the view in the default database for the current session. |
|
view_name |
The name of the view to be created. |
|
column_name |
The name of a view column. If more than one column is specified, list their names in the order in which each column is to be displayed for the view. |
|
LOCKING clause |
The specified lock is placed on the underlying base table set each time the view is referenced in an SQL statement. Recommendation: Create views with a LOCKING FOR ACCESS modifier to allow concurrent access of the data to read-only users and users who will modify the data. This prevents deadlocks and increases performance. ACCESS locking is a tool that can be used to reduce the isolation of a user request allowing that request to access valid, but uncommitted data that is subject to change. This allows users that are interested in a broad, statistical snapshot of the data, but that do not require precise results, to attain access to data while modifications to that data are occurring. This can greatly improve performance, because those users do not have to wait unnecessarily for the write operation to complete. |
|
SELECT clause |
The SELECT statement which obtains the data for the view. The following are some of the options and clauses you can use: |
|
|
DISTINCT option Returns only one row from any set of duplicate rows in the SELECT result. |
|
|
ALL option Returns all rows, including duplicate rows in the SELECT result. This is the default. |
|
SELECT clause (continued) |
TOP n or TOP m PERCENT option [WITH TIES] Restricts a view to only n rows or m percent of the rows in an underlying base table. If you do not specify an ORDER BY clause, then a TOP n clause only specifies that any n base table rows be returned, not the TOP n. This option provides a fast method to obtain a restricted, non-statistically random sample of table rows. |
|
|
FROM clause Specifies the set of base tables or views from which data for the view are selected. |
|
|
WHERE clause Specifies a conditional expression by which rows for the view are selected. |
|
|
GROUP BY clause Groups result rows by the values in one or more columns or by various extended GROUP BY operations on specified column expressions. |
|
|
HAVING clause Specifies a conditional expression by which the groups defined by a GROUP BY clause are selected. |
|
|
QUALIFY clause Specifies a conditional ordered analytical function by which rows for the view are selected. |
|
|
WITH CHECK OPTION clause Integrity constraint option that restricts the rows in the table that can be affected by an INSERT or UPDATE statement to those defined by the WHERE clause. This option only pertains to updatable views. Recommendation: Specify a WITH CHECK OPTION clause in all your updatable view definitions to prevent unauthorized modification of data rows. |
|
|
ORDER BY clause Specifies the order in which result rows are to be sorted. You can only specify an ORDER BY clause for a view definition if you also specify either the TOP n or the TOP m PERCENT option. |
Note: The example lists the major options for creating a view. You can modify your view definition by using the REPLACE VIEW statement.
To automate the creation of views by using BTEQ scripts, see “Using BTEQ Scripts to Create Database Objects” on page 86.
3 Set privileges on the view as follows:
Example : Creating a View to Limit User Access
The following statement creates a view of the Employee table so that it provides access only to the names and job titles of the employees in department 300:
CREATE VIEW Views_Database.Dept300 (Associate_Name, Job_Title) AS
SELECT Associate_Name, Job_Title
FROM Tables_Database.Employee
WHERE Dept_No = 300
WITH CHECK OPTION;
The WITH CHECK OPTION prevents using this view to insert a row into the Employee table, or to update any row of the Employee table where Dept_No is not 300.
Example : Creating a View to Generate a Report
The following statement creates a view which calculates the minimum, maximum, and average salary of each department and displays only those rows with an average salary of $35,000 or higher.
Because the view definition specifies an ACCESS lock for the table, the view returns valid, but uncommitted data that is subject to change, particularly if the view selects data at the same time another user attempts to modify the salary data in the Employee table.
This view is designed for users who need quick access to data, but do not need precise results.
CREATE VIEW Views_Database.Dept_Salary (Dept_No, MinSal, MaxSal, AvgSal)
AS LOCKING TABLE Tables_Database.Employee FOR ACCESS
SELECT Dept_No, MIN(Salary), MAX(Salary), AVG(Salary)
FROM Tables_Database.Employee
GROUP BY Dept_No
HAVING AVG(Salary) >= 35000;
Reference Information
|
For step... |
Information on... |
Is available in... |
|
2 |
|
SQL Data Definition Language Syntax and Examples |
|
2 |
the default database |
SQL Fundamentals |
|
2 |
the SELECT statement, including usage of the options and clauses |
SQL Data Manipulation Language |
|
2 |
|
SQL Data Definition Language Syntax and Examples |
|
2 |
|
SQL Data Manipulation Language |
|
2 |
specifying locking in views |
SQL Request and Transaction Processing |
|
2 |
CREATE RECURSIVE VIEW and REPLACE RECURSIVE VIEW |
SQL Data Definition Language Syntax and Examples |