Creating Views Procedure - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
The following procedure shows how to create views using BTEQ:
  1. Log on to Vantage 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 [,...] ) AS SELECT_clause;
    database_name
    The name of the database in which to create the view, if different from the current database.
    Recommendation: Make the database a Views Database or a database located within a Views Database. See 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 can 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 that gets the data for the view. The options and clauses you can use include the following:
    Option Description
    DISTINCT Returns only one row from any set of duplicate rows in the SELECT result.
    ALL Returns all rows, including duplicate rows in the SELECT result. This is the default.
    TOP n or TOP m PERCENT [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 get a restricted, non-statistically random sample of table rows.
    FROM Specifies the set of base tables or views from which data for the view are selected.
    WHERE Specifies a conditional expression by which rows for the view are selected.
    GROUP BY Groups result rows by the values in one or more columns or by extended GROUP BY operations on specified column expressions.
    HAVING Specifies a conditional expression by which the groups defined by a GROUP BY clause are selected.
    QUALIFY Specifies a conditional ordered analytical function by which rows for the view are selected.
    WITH CHECK OPTION 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 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.
  3. 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.