3.9.1 Specification format and rules for the CREATE VIEW statement

The CREATE VIEW statement defines a viewed table.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

CREATE-VIEW-statement ::= CREATE VIEW table-name [(column-name-list)] AS query-expression [LIMIT-clause]
 
  column-name-list ::= column-name[,column-name]...

To Page Top

(2) Explanation of specification format

table-name

Specifies the name of the viewed table to be defined. You cannot specify a name that is the same as a base table, or a viewed table that has already been defined. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

column-name-list

column-name-list ::= column-name[,column-name]...

Specifies the columns that will make up the viewed table.

column-name:

Specifies the name of a column that will make up the viewed table. The column names must be unique within a single viewed table.

Do not specify a character string in the EXPnnnn_NO_NAME format as a column name. Such a column name might duplicate a derived column name that is automatically set by HADB. In this format, nnnn is an unsigned integer in the range from 0000 to 9999.

Note the following points concerning column names:

The number of column names specified in column-name-list must be the same as the number of columns in the table derived by the query expression.
A maximum of 1,000 columns can be specified in column-name-list.
If column-name-list is omitted, the names of the columns that make up the viewed table will be the same as the names of the columns derived by the query expression. For details about derived column names, see 6.9 Derived column names.
You must specify column-name-list in the following circumstances:

• If the derived column names are not unique

• If one or more columns have no corresponding derived column name

● AS query-expression [LIMIT-clause]

Specifies a query expression that determines the contents that will make up the viewed table. For details about query expressions, see 7.1 Query expression.

All tables specified in the query expression become tables upon which the viewed tables will be based (underlying tables).

The following items cannot be specified in the query expression:

[table-specification.]ROW
Dynamic parameters

Note: If you execute the CREATE VIEW statement with * or table-specification.* in the selection list in the outermost query specification in the query expression, and then add a column to the underlying table, that column is not added to the viewed table.

LIMIT-clause:

Specifies the maximum number of rows that will be retrieved from the results of the query expression.

For details about the LIMIT clause, see 7.9 LIMIT clause.

To Page Top

(3) Privileges required at execution

To execute the CREATE VIEW statement, all of the following privileges are required:

CONNECT privilege
Schema definition privilege
SELECT privilege for all underlying tables that are to be specified in the query expression

To Page Top

(4) Rules

A maximum of 30,000 viewed tables can be defined in the system.
The maximum length of a CREATE VIEW statement is 64,000 bytes.
The total number of table names, derived tables, and table function derived tables in table references specified in the CREATE VIEW statement cannot exceed 2,047.

Note that if the following items are specified in a table reference, the total number of derived tables is checked for the SQL statement after those items are equivalently exchanged into internal derived tables:
- Query name
- Viewed tables
  
  If a viewed table is specified in the CREATE VIEW statement, the total number of derived tables is checked after the viewed table specified in the CREATE VIEW statement is equivalently exchanged into a derived table.
For rules and examples of how to count the number of tables, derived tables, and table function derived tables specified in an SQL statement, see (4) Rules in 4.4.1 Specification format and rules for the SELECT statement.
The total number of query specifications and table value constructors that can be included in the CREATE VIEW statement cannot exceed 1,023.
You cannot define a viewed table with a schema name that is different than the HADB user whose authorization identifier is connected to the HADB server.
The columns that comprise the viewed table will have the same attributes as the columns in the table from which the viewed table is derived as the result of executing the query expression in the CREATE VIEW statement. Note that these attributes are the data type, data length, and whether a NOT NULL constraint exists.
Viewed tables include read-only viewed tables and updatable viewed tables. You cannot insert, update, or delete rows in a read-only viewed table.
Whether a viewed table will be a read-only viewed table or an updatable viewed table depends on what is specified in AS query-expression. It will be a read-only viewed table in the following circumstances:
- If the outermost query specification includes a table join, a joined table, a derived table^#, table function derived table, SELECT DISTINCT, a GROUP BY clause, a HAVING clause, a window function, or a set function
- If the same column from the underlying table is specified multiple times in the selection expression in the outermost query specification
- If something other than a column specification is specified in the selection expression in the outermost query specification
- If the same table as the table specified in the FROM clause in the outermost query specification is specified in the FROM clause in a subquery
- If a read-only viewed table is specified in the FROM clause in the outermost query specification
- If you specify a set operation with the outermost query specification as an operand
- If you specify a LIMIT clause
- If a recursive query name is specified in the FROM clause in the outermost query specification
- If a dictionary table or system table is specified for the FROM clause in the outermost query specification
#

If the viewed table no longer meets the conditions for being a read-only viewed table once the derived table is expanded, it will become an updatable viewed table. For the rules about derived table expansion, see 7.30.3 Rules for derived table expansion.

You can check whether the viewed table you defined is a read-only viewed table or an updatable viewed table by searching the dictionary table. For details on how to check this, see Checking whether a viewed table is updatable in the HADB Setup and Operation Guide.
The access privilege for the viewed table that you define is determined based on the following rules:
- When an HADB user defines a viewed table, the user's access privileges to the viewed table are determined by the user's access privileges to all the underlying tables. For example, if you want to have the INSERT privilege on the viewed table you are defining, you must have the INSERT privilege on all of the underlying tables.
- This item explains the rule to determine the access privilege for a viewed table defined by specifying either of the following elements in the query expression of the CREATE VIEW statement:
  - Table value constructors
  - Table function derived table
  Any HADB users who define a viewed table for a derived table derived by a table value constructor or for a table function derived table are assumed to have an access privilege with the grant option. Therefore, if another underlying table of the viewed table is specified in the query expression of the CREATE VIEW statement, the access privilege for the defined viewed table is as described later.
  
  Examples:
  - If a table value constructor and an underlying table having access privileges with the grant option are specified in the query expression of the CREATE VIEW statement
    
    For the viewed table, the access privilege that overlaps between the access privilege for the derived table derived by the table value constructor and the access privilege for the underlying table of the viewed table is applied. In this case, therefore, the HADB user who defines a viewed table will have the access privilege with the grant option for the viewed table.
  - If a table value constructor and an underlying table that has only the SELECT privilege are specified in the query expression of the CREATE VIEW statement
    
    For the viewed table, the access privilege that overlaps between the access privilege for the derived table derived by the table value constructor and the access privilege for the underlying table of the viewed table is applied. In this case, therefore, the HADB user who defines a viewed table will have only the SELECT privilege for the viewed table.
An HADB user who wants to grant an access privilege for a viewed table to another HADB user must have an access privilege with the grant option for all underlying tables of that viewed table.
When a new access privilege for an underlying table is granted, the access privilege for the viewed tables that depend on the underlying table is also granted. (Consequently, propagation of access privileges will occur.) For example, assume that HADB user A has defined viewed table A.V1 by using table X.T1 as the underlying table, and viewed table A.V2 by using viewed table A.V1 as the underlying table. In this case, if the INSERT privilege for table X.T1 is granted to HADB user A, INSERT privilege for viewed tables A.V1 and A.V2 is also granted to HADB user A.
If the access privilege for an underlying table is revoked, the access privilege for the viewed tables that depend on the underlying table is also revoked.
If you define a viewed table by specifying the scalar function CONTAINS (with synonym-search specification) in the query expression of the CREATE VIEW statement, the following rules apply:
- If you delete the synonym dictionary that was specified in the synonym-search specification, an error occurs when the viewed table is accessed.
- If you update the synonym dictionary that was specified in the synonym-search specification, the updates are applied to the viewed table that you define.
Assume that you have defined a viewed table for which the query name in the WITH clause specified in the query expression in the CREATE VIEW statement is not referenced in the CREATE VIEW statement. In this case, when you search the viewed table, the following rules apply. Also, the following rules apply when you specify the viewed table in another CREATE VIEW statement.
- The upper limit check on the number of constituent elements is not conducted for the number of constituent elements in the query expression body for a query name that is not referenced in the CREATE VIEW statement.
- No lock is obtained for any tables specified in the query expression body for a query name that is not referenced in the CREATE VIEW statement.
The following shows examples.

(Example) When a viewed table is defined
```
CREATE VIEW "V1"
    AS WITH "Q1" AS (SELECT "T1"."C1","T2"."C2" FROM "T1","T2")
       SELECT * FROM "T3"
```
[Explanation]

The rules shown earlier are not applied when a viewed table is defined. Therefore, the number of queries specified in the CREATE VIEW statement is 2 (the query corresponding to query name Q1 and its main query). Also, the number of specified tables is 3 (base tables T1, T2, and T3).

(Example) When a viewed table is searched
```
SELECT * FROM "V1"
```
[Explanation]
- The rules shown earlier are applied when a viewed table is searched. Therefore, the number of queries specified in the SELECT statement is 2 (the main query and derived query for the derived table equivalently exchanged from viewed table V1).
- The number of specified tables is 2 (the derived table equivalently exchanged from viewed table V1 and base table T3 in the derived query for the derived table). The upper limit check on the number of queries and tables that can be specified in one SQL statement is conducted based on these rules.
- For base tables T1 and T2, no lock is obtained.

In the query expression of the CREATE VIEW statement, subqueries can be specified in a nested form. In this case, the subquery nesting depth must not exceed 31. Note that if the table specified in the FROM clause is a viewed table, the subquery nesting depth must not exceed 31 after HADB generates the internal derived table specified in the underlying query expression. For details, see (a) Common rules for subqueries in (4) Rules in 7.3.1 Specification format and rules for subqueries.

Example 1:

CREATE VIEW "V1"
  AS SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM "T1") AS DT32
     ) AS DT31 ) AS DT30 ) AS DT29 ) AS DT28 ) AS DT27 ) AS DT26 ) AS DT25 ) AS DT24
     ) AS DT23 ) AS DT22 ) AS DT21 ) AS DT20 ) AS DT19 ) AS DT18 ) AS DT17 ) AS DT16
     ) AS DT15 ) AS DT14 ) AS DT13 ) AS DT12 ) AS DT11 ) AS DT10 ) AS DT9 ) AS DT8
     ) AS DT7 ) AS DT6 ) AS DT5 ) AS DT4 ) AS DT3 ) AS DT2 ) AS DT1 ) AS DT0

In the preceding example, the subquery nesting depth of viewed table V1 is 32. In this case, because the maximum nesting depth is exceeded, the CREATE VIEW statement will result in an error.

Note that in this example, T1 is the base table.

Example 2:

CREATE VIEW "V2"           <== Viewed table V2
  AS SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (
     SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM "T1") AS DT30
     ) AS DT29 ) AS DT28 ) AS DT27 ) AS DT26 ) AS DT25 ) AS DT24 ) AS DT23 ) AS DT22
     ) AS DT21 ) AS DT20 ) AS DT19 ) AS DT18 ) AS DT17 ) AS DT16 ) AS DT15 ) AS DT14
     ) AS DT13 ) AS DT12 ) AS DT11 ) AS DT10 ) AS DT9 ) AS DT8 ) AS DT7 ) AS DT6
     ) AS DT5 ) AS DT4 ) AS DT3 ) AS DT2 ) AS DT1 ) AS DT0
CREATE VIEW "V3" AS SELECT * FROM "V2"       <== Viewed table V3
CREATE VIEW "V4" AS SELECT * FROM "V3"       <== Viewed table V4

For viewed table V2, the subquery nesting depth is 30. Therefore, the CREATE VIEW statement can run.
For viewed table V3, the subquery nesting depth becomes 31 when the internal derived table is generated. Therefore, the CREATE VIEW statement can run.
For viewed table V4, the subquery nesting depth becomes 32 when the internal derived table is generated. In this case, because the maximum nesting depth is exceeded, the CREATE VIEW statement will result in an error.

Note that in this example, T1 is the base table.

A viewed table whose view level is 33 cannot be specified in the query expression for the CREATE VIEW statement.
When the version of the HADB server is upgraded, the viewed tables that depend on dictionary tables or system tables might be re-created automatically. For details about the conditions in which viewed tables are re-created, see Re-creation of viewed tables in the event of a version upgrade in Notes on version upgrading in the HADB Setup and Operation Guide.
The viewed tables defined after a transaction is started cannot be accessed from the transaction.

To Page Top

(5) Examples

Example 1

Define a viewed table of shops (VSHOPSLIST) from which you can retrieve all the columns in the shops table (SHOPSLIST) except the address column (ADDRESS). Let the ordering and column names of the columns that make up the viewed table of shops be as follows:

Shop code (SHOP_CODE)
Region code (RGN_CODE)
Shop name (SHOP_NAME)
Telephone number (TEL_NO)

CREATE VIEW "VSHOPSLIST" ("SHOP_CODE","RGN_CODE","SHOP_NAME","TEL_NO")
    AS SELECT "SHOP_CODE","RGN_CODE","SHOP_NAME","TEL_NO"
           FROM "SHOPSLIST"

The viewed table of shops (VSHOPSLIST) is an updatable viewed table.

Example 2

This example defines (as a viewed table) a sales table (VSALES) that obtains the maximum sales value (QMAXSALES) for each product name (PUR-NAME) from the sales history table (SALESLIST) and product table (PRODUCTSLIST). Let the structure of columns in the sales table (VSALES) be as follows:

Product name (VPUR_NAME)
Maximum sales value (VQMAXSALES)

■ Defining a viewed table

CREATE VIEW "VSALES" ("VPUR-NAME","VQMAXSALES")
    AS WITH "QT1"("QCODE","QMAXSALES") AS (SELECT "PUR-CODE",MAX("PRICE" * "QUANTITY")
                                             FROM "SALESLIST"
                                               GROUP BY "PUR-CODE")
       SELECT "PUR-NAME","QMAXSALES"
         FROM "QT1" INNER JOIN "PRODUCTSLIST" ON "QCODE"="PUR-CODE"

[Figure]

■ Searching a viewed table

SELECT * FROM "VSALES"

[Figure]

To Page Top