The following procedure shows how to create views using BTEQ:
- Log on to Teradata Vantage as user DBADMIN using BTEQ.
- 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.
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 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.
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. - Set privileges on the view as follows:
- Provide general users read-only access so they can use the view to query data.
- Provide privileged users read and write access so they can use the view to update data in the underlying tables.