7.11.1 Specification format for table references

(1) Specification format

table-reference::={table-primary|joined-table}
 
  table-primary::={table-name [[AS] correlation-name][index-specification]
                |query-name [[AS] correlation-name]
                |derived-table [[AS] correlation-name [(derived-column-list)]]
                |table-function-derived-table [AS] correlation-name [(table-function-column-list)]
                |(joined-table)}
 
    derived-table::={table-subquery|(table-value-constructor)}
    derived-column-list::=column-name[,column-name]...
 
    table-function-derived-table::=TABLE(system-defined-function)
    table-function-column-list::=column-name data-type[,column-name data-type]...

To Page Top

(2) Explanation of specification format

table-name:

Specifies the table from which to retrieve data. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

To retrieve data from a dictionary table or system table, specify the schema name MASTER.

If an archivable multi-chunk table is specified, accesses to the location table and system table (STATUS_CHUNKS) occur. At this time, locked resources are secured for the system table (STATUS_CHUNKS). For details about locks, see Locking in the HADB Setup and Operation Guide.

[AS] correlation-name:

Specifies a name assigned to separately identify a table for one of the following purposes:

• To join a table to itself

• To reference a column of the same table inside a subquery

Note the following points:

• When specifying a table function derived table, a correlation name is required.

• To specify the same scope variable multiple times in one FROM clause, specify correlation names so that each scope variable is able to uniquely identify the column specification it qualifies.

• The correlation name specified in one FROM clause must be different from all scope variables specified in that clause. The name must also be different from the table identifiers of the scope variables. For details about the effective scope of scope variables, see 6.8 Scope variables.

• The retrieval results will be the same regardless of whether AS is specified.

• If the correlation name of a derived table is not specified, the correlation name is automatically assigned in the following format:

  ##ADD_DRVTBL_xxxxxxxxxx

  In the preceding format, xxxxxxxxxx is a 10-digit integer. This correlation name is displayed in the access path execution results.

  For table references with the same effective scope, do not specify a table name or correlation name that begins with ##ADD_DRVTBL_.

  Note

  If you do so, the HADB server might automatically assign a correlation name that is the same as a name that you specified.

index-specification:

Specifies a B-tree index or text index to be used when retrieving from a base table. Alternatively, it specifies that the use of a B-tree index or text index is to be suppressed. For details about index specifications, see 7.14 Index specification.

query-name:

Specifies a query name. For details about query names, see (a) WITH-clause in (2) Explanation of specification format in 7.1.1 Specification format and rules for query expressions.

derived-table:

Specifies a derived table in the format of a table subquery or table value constructor. For details about subqueries, see 7.3 Subqueries. For details about table value constructors, see 7.17 Table value constructors.

A derived table is a table that is derived as a result of a table subquery or table value constructor. The n-th column of a table subquery or table value constructor becomes the n-th column of a derived table.

A query specification that contains a derived table will be converted to an equivalent query specification that does not contain the derived table.

Assume that the (user-specified or automatically assigned) correlation name of the derived table is the derived query name, and the query expression of the derived table is the derived query expression. In this case, the derived query expression is treated as an internal derived table, following the rules for derived table expansion. For the rules for derived table expansion, see 7.30 Internal derived tables.

In addition, note the following concerning derived-table:

• If the correlation name of a derived table is not specified, the scope variable of that derived table has the effective scope but has no name (the HADB server internally generates a name, which users cannot check). Therefore, if there are two or more table references that have the same column name in the same effective scope, explicitly specify correlation names.

  Example of an SQL statement that generates an error:

  SELECT "C1" FROM "T1",(SELECT "C1" FROM "T1")

  For the underlined column (C1), there are multiple table references that have the same column name in the same effective scope (table T1 and the derived table). In this case, it is impossible to identify whether the underlined column (C1) is column C1 of table T1 or column C1 of the derived table. Therefore, the SQL statement will result in an error. In such a case, to reference a column of a derived table, specify a correlation name for the derived table, and qualify the column name with that correlation name. The following shows examples.

  Example of a correct SQL statement:

  SELECT "DT1"."C1" FROM "T1",(SELECT "C1" FROM "T1") AS "DT1"

• You cannot specify the row interface (ROW) for a derived table.

derived-column-list:

Specify the column name of each column of the derived table. Specify derived-column-list in the following format:

column-name[, column-name]...

The column names of the table derived by a query specification vary depending on whether derived-column-list is specified. For the rules concerning derived column names, see 6.9 Derived column names.

In addition, note the following concerning derived-column-list:

• If derived-column-list is omitted, the column names derived from the results of the table subquery must be unique.

• The column names in derived-column-list must be unique.

• Do not specify a character string in the EXPnnnn_NO_NAME format as a column name in derived-column-list. 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.

• If derived-column-list is specified, the number of column names in derived-column-list must be the same as the number of columns in the derived table.

• Make sure that the number of columns specified in derived-column-list does not exceed 1,000.

• Make sure that the number of columns derived by table subqueries or table value constructors does not exceed 1,000.

table-function-derived-table:

table-function-derived-table::=TABLE(system-defined-function)

A table function derived table is a collection of data in table format derived by means of a system-defined function. For details about system-defined functions, see 7.15 System-defined functions.

The rules for specifying a table function derived table are as follows:

• To specify a table function derived table as a table reference, specify the table function derived table's correlation name.

• You cannot specify the row interface (ROW) for a table function derived table.

table-function-column-list:

table-function-column-list::=column-name data-type[,column-name data-type]...

Specifies the name and data type of each column in the table function derived table.

The rules for specifying a table function column list are as follows:

• If you specify the ADB_AUDITREAD function for a table function derived table, you cannot specify a table function column list.

• If you specify the ADB_CSVREAD function for a table function derived table, you must specify a table function column list.

• For the specification format of each data type, see Table 3‒8: Data types that can be specified (CREATE TABLE statement).

• The column names in a table function column list must be unique.

• Do not specify a character string in the EXPnnnn_NO_NAME format as a column name in a table function column list. 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.

• The number of columns in a table function column list must not exceed 1,000.

• You cannot specify VARCHAR-type data whose length exceeds 32,000 bytes for a table function column list.

For rules on derived column names, see (4) In the case of a table function derived table in 6.9.2 Decision rules for derived column names in query results.

joined-table:

Specifies a joined table. For details on joined tables, see 7.12 Joined tables.

To Page Top

(3) Examples

The following examples illustrate table references.

Example 1

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased products on or after September 6, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-06'

The underlined portion indicates the table reference.

Example 2

Search the dictionary table (SQL_INDEXES) to find the names of indexes (INDEX_NAME) that are defined for the sales history table (SALESLIST).

SELECT "INDEX_NAME"
    FROM "MASTER"."SQL_INDEXES"
        WHERE "TABLE_NAME"='SALESLIST'

The underlined portion indicates the table reference. To search the dictionary table, you must qualify the table name with the schema name MASTER.

Example 3

Search the dictionary table (SQL_INDEXES) to find the names of indexes (INDEX_NAME) that are defined for the sales history table (SALESLIST) using IDX as a correlation name.

SELECT "IDX"."INDEX_NAME"
    FROM "MASTER"."SQL_INDEXES" AS "IDX"
        WHERE "IDX"."TABLE_NAME"='SALESLIST'

The underlined portion indicates the table reference.

Example 4

Retrieve the customer ID (USERID), product code (PUR-CODE), customer name (NAME), and sex (SEX) from the sales history table (SALESLIST) and customer table (USERSLIST), joined together with the customer ID column (USERID) as the search condition.

SELECT "SALESLIST"."USERID","PUR-CODE","NAME","SEX"
    FROM ("SALESLIST" JOIN "USERSLIST"
           ON "USERSLIST"."USERID"="SALESLIST"."USERID")

The underlined portion indicates the table reference.

Example 5

Extract the following data from a CSV file (/dir/file.csv.gz) compressed in GZIP format:

• Customer ID (USERID)

• Customer name (NAME)

• Age (AGE)

SELECT "USERID","NAME","AGE"
    FROM TABLE(ADB_CSVREAD(MULTISET ['/dir/file.csv.gz'],
                          'COMPRESSION_FORMAT=GZIP;'))
         AS "USERLIST" ("USERID" CHAR(5),
                        "NAME" VARCHAR(100),
                        "AGE" INTEGER,
                        "COUNTRY" VARCHAR(100),
                        "INFORMATION" VARBINARY(10))

The underlined portion indicates the table reference.

To Page Top