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 [,...] ) AS SELECT_clause;
- database_name
- The name of the database where the view will be created if it is different from the current database.
- 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.
- SELECT_clause
- The SELECT statement which obtains the data for the view. The following are some of the options and clauses you can use:
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 obtain 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 various 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. - 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.