CREATE VIEW (Define view)

Function

CREATE VIEW defines a view table.

It can also define view tables (public views) that can be used by any user, using the same table identifier not qualified with an authorization identifier.

(1) CREATE VIEW (Define view)

Privileges

Users who can use derived query expressions
These users can specify available derived query expressions and define their own view tables.
The following users are permitted to use derived query expressions (for details of derived query expressions, see 2.2 Query expressions; for details of the privilege for specifying a query in a derived query expression, see the privileges topic in 2.3 Query specification).
  • Owner of a base table, foreign table, or view table
  • User with the SELECT privilege on base tables, foreign tables, or view tables
If a user defines a view table by receiving the SELECT privilege for tables owned by another user, only the receiving user, not the user who granted the SELECT privilege, can perform view definitions from that view table. If the directory server linkage facility is used, view tables cannot be defined even when using the SELECT privilege that is granted to a role.

Format

CREATE [READ ONLY] VIEW [authorization-identifier.] table-identifier
     [(column-name [,column-name]...)]
 AS derived-query-expression

derived-query-expression::= query-expression-body
query-expression-body :: = {query-specification
         |(query-expression-body)
         |query-expression-body {UNION | EXCEPT}[ALL]
          {query-specification|(query-expression-body)}}

Operands

Specifies that the view table being defined is to be a read-only table.

authorization-identifier
Specifies the authorization identifier of the user who is to own the view table being defined.
table-identifier
Specifies a name for the view table being defined.
This name must not already be specified for a table (base table, foreign table, or view table) owned by the user specified as the owner of the view table being defined.
column-name
Specifies a name for a column that is to comprise the view table.
If no column name is specified, the default column names are as explained below:
  • If no set operation is specified in the derived query expression, the column names of the columns for the derived table specified in the query specification (when AS column-name is specified, the column names specified in the AS clause) will be the column names of the columns that constitute the view table.
  • When a set operation is specified in the derived query expression, the column names of the columns for the derived table specified in the first query specification (when AS column-name is specified, the column names specified in the AS clause) of the derived query expression will be the column names of the columns that constitute the view table.
A column name must be specified if the derived table specified in the derived query expression contains either multiple columns with the same name or unnamed columns.
The following rules apply to the column names:
  1. When the column in the derived table is a column derived from one of the following items, and when the specification of AS column-name is omitted, the column becomes a nameless column:
    [Figure]Scalar operation
    [Figure]Function call
    [Figure]Set functions
    [Figure]Literals
    [Figure]USER
    [Figure] CURRENT_DATA
    [Figure] CURRENT_TIME
    [Figure] CURRENT_TIMESTAMP[(p)]
  2. Each column name must be unique. The number of column names specified should be the same as the number of columns in the derived table obtained as a result of the derived query expression.
  3. The maximum number of column names that can be specified is 30,000.

Specifies a derived query expression that expresses the contents of the definition of a view table (for details of search conditions, see the following sections in this manual: 2.2 Query expressions, 2.3 Query specification, 2.5 Table expressions, and 2.7 Search conditions).

The following rules apply to the derived query expression:

  1. [table-specification.] ROW cannot be specified in a selection expression in a derived query expression for a view definition.
  2. A subscripted repetition column in a directly contained SELECT clause cannot be specified in a derived query expression for a view definition.
  3. Even though * or table.* is specified in a SELECT clause that is directly contained in a derived query expression, columns that are added to the base table for a view table after definition of the view table will not be added to the view table.
  4. Base tables, foreign tables, and view tables can be specified in a derived query expression for a view definition. Similarly, a new table based on a view table can be defined.
  5. Embedded variables and the ? parameter cannot be specified in a search condition in a derived query expression for a view definition.
  6. Plug-in functions and component specifications cannot be specified in a derived query expression in a view definition.
  7. Value expressions containing any of the following data types cannot be specified in a derived query expression in a view definition:
    [Figure]BOOLEAN
    [Figure] Abstract data type
  8. A table alias, a table belonging to another RD-node, or a view table belonging to another RD-node cannot be specified in a derived query expression in a view definition.

Common rules

  1. A view table can be a read-only view table or a writable view table. Operations such as inserting, updating, and deleting rows, or specifying the FOR UPDATE clause in a cursor declaration, cannot be performed on a read-only view table.
    The following view tables are read-only:
    • View tables that are defined by specifying READ ONLY in a view definition statement
    • View tables that include a table join, SELECT DISTINCT, a GROUP BY clause, a HAVING clause, or a set function with respect to the outermost query specification in a view definition statement.
    • View tables that are defined by specifying the same column in the base table more than once in a SELECT clause in a view definition statement.
    • View tables that include a value expression other than a column specification in the SELECT clause in the outermost query specification in a view definition statement
    • Of the view tables defined before Version 07-02, a view table that contains a subquery that specifies in the FROM clause the same table as the FROM clause in the outermost query specification in a view definition statement (if the table specified in the FROM clause is a view table, includes tables that are a base for the view table).
      Note: To make the view table an updatable table, delete the view table, and then redefine it.
    • A view table that is defined by specifying a derived table in the FROM clause in the outermost query specification in a view definition statement.
    • A view table defined by specifying a set operation in a view definition statement.
    All other view tables (those that are not read-only) are writable view tables.
  2. The table that is specified in the derived query expression for a view definition serves as the base table that comprises the view table. The table that is specified in a FROM clause in the derived query expression for a view definition is the base table from which the view table is derived.
  3. The table that is specified in a FROM clause contained in the outermost query in a derived query expression for a view definition is the base table that is subject to operations on that view table.
    The owner of an updatable view table directly inherits the following access privileges with respect to the base table subject to operations on the view table:
    • SELECT privilege
    • INSERT privilege
    • DELETE privilege
    • UPDATE privilege
    The owner of a read-only view table inherits only the INSERT privilege with respect to the base table that is subject to operations on the view table.
    The owner of a view table that is defined from tables owned by that user can grant and revoke the same access privileges for other users.
  4. Rows that are added or updated using a view table need not satisfy the search conditions specified in the derived query expression during definition of the view table. However, rows that do not satisfy the search conditions specified in the derived query expression during definition of the view table cannot be searched, updated, or deleted by using the view table.
  5. The attributes (data type, data length, any NOT NULL constraints that may be in effect, and maximum number of elements) of columns comprising a view table will be the same as the attributes of the corresponding columns in the derived table specified in the derived query expression for the view definition.
  6. The table that is the base table for a view table must be defined before the view definition can be executed.
  7. View definition statements cannot be specified from an X/Open compliant UAP running under OLTP.
  8. A column in a view table, defined in terms of a date, time, or time stamp literal represented in a character string, or a date interval or time interval literal represented in a decimal number, are treated in terms of the data type for that column, even when the column is specified in a location where date, time, time stamp, date interval, or time interval data is required; it is not converted into the respective required data type, except when such a column is specified as an argument in the scalar function DATE, TIME, or TIMESTAMP.
    Example:

     UPDATE T1 SET C1=(SELECT VC2 FROM V1 WHERE VC1='E')
     ... Cannot be specified.

    (C1 is a column of date data type, VC2 is a column of VARCHAR(10) defined in terms of a literal that is represented in a date character string)
  9. A subscripted repetition column in a directly contained SELECT clause cannot be specified in the derived query expression for a view definition.
  10. If a CASE expression is specified in a selection expression in the outermost query specification, a repetition column cannot be specified in the CASE expression in a derived query expression for a view definition.
  11. Any of the following items cannot be specified in a selection expression in a derived query expression in a view definition:
    • WRITE specification
    • GET_JAVA_STORED_ROUTINE_SOURCE specification
    • SUBSTR scalar function that produces results of the BLOB data type
    • Function calls that produce results of the BLOB data type
    • Window function
  12. Scalar operations, literals, function calls, and scalar subqueries of the following attributes cannot be specified in a selection expression in the outermost query in the view definition, or in a query selection expression that is subject to set operations:
    • BLOB
    • BINARY with a maximum length of 32,001 bytes or greater
  13. If a view table defined by specifying function call is operated, user-defined functions that are candidates for function call are solely user-defined functions that were defined before the view table was defined.
  14. If the platform is moved from a 32-bit mode HiRDB to a 64-bit mode HiRDB using the SQL object migration utility, view tables meeting all of the following conditions may produce different search results before and after migration:
    (1) Defining the view table by specifying a user-defined function in a derived query expression in the view definition
    (2) After defining a view table in (1), defining a user-defined function that can be a calling candidate for the user-defined function specified in the derived query expression in (1)
    (3) The user-defined function defined in (2) has higher calling priority than the user-defined function specified in the derived query expression in (1)
    After performing steps (1) to (3) above, use the SQL object migration utility to move from 32-bit mode HiRDB to 64-bit mode HiRDB.
    For rules on the determination of which user-defined function is called, see (6) Rules for determining the function to be called and the data type of the result in 2.20 Function calls.

Examples

  1. From a stock table (STOCK), define a view table (VSTOCK1) composed of the product code (PCODE), quantity in stock (SQTY), and unit price (PRICE) columns for the rows containing socks in the product name (PNAME) column; assume that the columns are sorted on product code, quantity in stock, and unit price:

    CREATE VIEW VSTOCK1
      AS SELECT PCODE,SQTY,PRICE
            FROM STOCK
            WHERE PNAME=N'socks'

  2. Define a read-only view table (VSTOCK2) with the same organization as the STOCK stock table:

    CREATE READ ONLY VIEW VSTOCK2
      AS SELECT * FROM STOCK

(2) CREATE PUBLIC VIEW (Define public view)

Privileges

Owner of a base table
The owner of a base table can define a public view owned by him or her from his or her own base table.
Owner of a foreign table
The owner of a foreign table can define a public view owned by him or her from his or her own foreign table.
Owner of a view table
The owner of a view table can define a public view owned by him or her from a view table based on his or her own base table and foreign table.

Format

CREATE PUBLIC[READ ONLY] VIEW table-identifier
    [(column-name[, column-name]...)]
 AS derived-query-expression

derived-query-expression::= query-expression-body
query-expression-body::= {query-expression
          | (query-expression-body)
          | query-expression-body {UNION|EXCEPT} [ALL]
           {query-expression | (query-expression-body)}}

Operands

For an explanation of the operands other than PUBLIC and table-identifier, see (1) CREATE VIEW.

Specify this operand when defining a view table as a public view.

When a view table is defined as a public view, a single view table can be used by more than one user by specifying a table identifier, without the need to define view tables of identical contents for different users.

Specifies the name of the public view to be defined.

In table-identifier, a name identical to a previously defined public view cannot be specified.

Common rules

  1. As the name of a public view being defined, the same table identifier as that for a previously defined table (base table, foreign table, or view table) can be specified. However, if a table identifier is used by omitting an authorization identifier, the tables (base tables, foreign tables, or view tables) owned by the user who is executing the UAP take precedence over the public view. When qualifying a public view, in authorization-identifier, specify the word PUBLIC in upper case characters enclosed in double quotation marks (").
  2. The other rules are the same as the common rules given in (1) CREATE VIEW.

Notes

  1. In columns (such as the TABLE_SCHEMA column in the SQL_TABLES table) that store the owner of a dictionary table, the word PUBLIC is assigned. The authorization identifier used to define the public view is stored in the TABLE_CREATOR column in the SQL_TABLES table.
  2. If a definition SQL statement specifying a table (base table, foreign table, or view table) with the same name as the public view being used is issued while preprocessing on an SQL statement using the public view is in effect, the definition SQL statement goes into a lock release wait state.
  3. After defining a procedure and a trigger using a public view, if a table (base table, foreign table, or view table) having the same name as the public view is defined, the procedure and the trigger are not disabled, and they operate as the procedure and trigger that use the public view. However, if the procedure and trigger are recreated (including the case in which a procedure with a nullified index is internally created by HiRDB), they operate as the procedure and trigger that use the table (base table, foreign table, or view table) having the same name as the public view.
  4. Public views can be deleted using DROP PUBLIC VIEW.

Examples

  1. From an inventory table (STOCK) define a public view comprised of a row for which the product name (PNAME) is socks, and columns of product code (PCODE), quantity in stock (SQTY), and unit price (PRICE), such that the columns are sorted by product code, quantity in stock, and unit price:

     CREATE PUBLIC VIEW PVSTOCK1
         AS SELECT PCODE,SQTY,PRICE
         FROM STOCK
         WHERE PNAME = N'socks'