Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 170 171 172 173 174 175 < 176 > 177 178 179 180 181 182 .. 241 >> Next


REPLACE VIEW

Syntax Element... Specifies . . .
CROSS JOIN all rows from all tables specified in the FROM clause.
single_table the name of table participating in join.
Derived Tables

The derived table allows the FROM list to specify a spool file, comprised of selected data from an underlying table(s). The derived table acts like a viewed table. This is an extension of a current functionality. It is a full ANSI SQL feature.

(subquery) nested SELECT statements.
AS an introductory clause to derived table name.
derived_tname the name of derived table.
col_name the column name. The col_name field is for the column name only; forms such as Databasename.Tablename.Columnname, or Tablename.Columnname should not be used.
WHERE a keyword introducing the search condition in the SELECT statement.
search_cond a conditional search expression that must be satisfied by the row(s) returned by the statement.
GROUP BY a keyword introducing a reference to one or more expressions in the select expression list. This reference defines a group for which an aggregate operation is specified in the expression list.
col_name Col_name specifies the name(s) of columns used in the GROUP BY clause in the SELECT statement.
col_pos the numeric position of the column(s) used in the GROUP BY clause.
expr the expression(s) used in the GROUP BY clause.
HAVING a keyword introducing the conditional clause in the SELECT statement.
cond one or more conditional expressions that must be satisfied by the result rows. Aggregate operators may be used with HAVING. HAVING cond selects rows from a single group defined in the select expr-list that has only aggregate results, or it selects rows from the group or groups defined in a GROUP BY clause.
ORDER BY a keyword specifying the order in which result rows are to be sorted.
col_name the name(s) of columns used in the ORDER BY clause in the SELECT statement. These can be ascending or descending.
col_pos the numeric position of the column(s) used in the ORDER BY clause. This can be ascending or descending.
expr an expression in the SELECT expr-list, either by name, or by means of a constant that specifies the numeric position of the expression in the expr-list.

Teradata RDBMS for UNIX SQL Reference

8-229
Teradata SQL Syntax Guide

REPLACE VIEW

Syntax Element... Specifies . . .
ASC DESC the sort order. The default order is ASC. ASC specifies ascending order; DESC specifies descending order. If a sort option is not given, result values are sorted in ascending order according to the client system’s collating sequence. If ORDER BY is not specified, rows are returned unsorted.
WITH CHECK OPTION Restricts the rows that can be accessed in the table by an INSERT or UPDATE statement. Refer to the section “WITH CHECK OPTION Clause in Views” below.

Usage Notes

To replace an existing view or macro, you must have the DROP VIEW privilege.

You must have the CREATE VIEW privilege if the specified view or macro does not exist.

In effect, the REPLACE VIEW statement operates as a DROP followed by a CREATE, except for the handling of access rights. All access rights that were granted directly on the original view or macro are retained.

If the specified view or macro does not exist, the REPLACE VIEW statement creates the view or macro. In this case, the REPLACE statement has the same effect as a CREATE VIEW statement.

Unqualified object names in macros that contain DML statements are forever bound to the creating user’s default database at the time of creation.

See also “CREATE VIEW”.

Limit On Request Size

Because expansions of source text in view definitions are made to fully qualify object names and to normalize expressions, it is possible for a view to be defined but to be unusable because of stack overflows in the Syntaxer at execution. In this case, the system returns a 3741 error.

Examples

Example 1

The following examples illustrate the use of and REPLACE VIEW:

To change the department name column in the employee_info view to a department number column, enter:

REPLACE VIEW employee_info

(Number, Name, Position, Department)

AS SELECT employee.empno, name, jobtitle, deptno WHERE jobtitle NOT IN ('vice pres', 'manager') ;

You must have the drop privilege on a view to replace it.

8-230

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

REPLACE VIEW

If you enter a REPLACE VIEW statement for a view that does not exist, the system creates the view following the specifications in the REPLACE statement.

You can display and change the current definition of a view by Example 2 employing the SHOW VIEW statement. As an example, the

statement:

SHOW VIEW employee_info

displays the most recent CREATE or REPLACE statement for the employee_info view in the display area.

Teradata RDBMS for UNIX SQL Reference

8-231
Teradata SQL Syntax Guide

REVOKE

REVOKE

The REVOKE statement rescinds privileges from one or more users. The privileges may have been conferred automatically or by a previous GRANT statement.
Previous << 1 .. 170 171 172 173 174 175 < 176 > 177 178 179 180 181 182 .. 241 >> Next