1.1.8 Qualifying a name

You can use a name qualifier to do things like explicitly specifying an authorization identifier or making a name unique. A name qualifier concatenates an authorization identifier to a table identifier, using a character such as a period (.) between the two strings.

Organization of this subsection
(1) Table name, index name, index type name, user-defined type name, routine name, trigger name, and sequence generator name
(2) Table specification
(3) Column specification

(1) Table name, index name, index type name, user-defined type name, routine name, trigger name, and sequence generator name

Explanations of these names and their formats are given as follows:

Table name:
A table identifier qualified with an authorization identifier.
Index name:
An index identifier qualified with an authorization identifier.
Index type name:
An index type identifier qualified with an authorization identifier.
User-defined type name:
A data type identifier qualified with an authorization identifier.
Routine name:
A routine identifier qualified with an authorization identifier.
Trigger name:
A trigger identifier qualified with an authorization identifier.
Sequence generator name:
A sequence generator identifier qualified with an authorization identifier.

table-name::=[authorization-identifier.]table-identifier
index-name::=[authorization-identifier.]index-identifier
index-type-name::=[authorization-identifier.]index-type-identifier
user-defined-type-name::=[authorization-identifier.]data-type-identifier
routine-name::=[authorization-identifier.]routine-identifier
trigger-name::=[authorization-identifier.]trigger-identifier
sequence-generator-name::=[authorization-identifier.]sequence-generator-identifier

ItemSpecificationRules
Authorization identifierIf you are specifying your own table identifier, index identifier, index type identifier, data type identifier, routine identifier, or trigger identifier, specify your own authorization identifier. If the relevant identifier is owned by another user, specify that user's authorization identifier. However, if you specify the name of a public view as the table identifier, or the name of a public function or public procedure as the routine identifier, specify PUBLIC.If you omit the authorization identifier, it is determined as follows:
  • When a utility is executed:
    Authorization identifier of the user who starts the utility.
  • When a UAP is executed:#1
    If you omit the authorization identifier in a UAP, it is determined in the following order.
  1. The authorization identifier specified in pre-processing
  2. The authorization identifier specified in an operand of the CONNECT statement
  3. If nothing is specified in the CONNECT statement's operands, the authorization identifier specified in the client environment definition PDUSER
  4. PUBLIC (when a table identifier or routine identifier is specified#2)
Table identifierSpecify the name of a base table or view table.
Index identifierSpecify the name of an index.
Index type identifierSpecify the name of an index type.
Data type identifierSpecify the name of a user-defined type.
Routine identifierSpecify the name of a procedure or function.
Trigger identifierSpecify the name of a trigger.
Sequence generator identifierSpecify the name of a sequence generator.
#1
When the authorization identifier is omitted from an SQL character string specified in a definition SQL, PREPARE statement, or EXECUTE IMMEDIATE statement, the authorization identifier that is assumed is determined in the following priority:
  1. Authorization identifier that was in effect during CONNECT
  2. Authorization identifier that was specified in a client environment variable
  3. UAP user
  4. PUBLIC (for a table identifier or routine identifier defined outside of definition SQL)
#2
For details about using the authorization identifier MASTER for a routine identifier, see 1.1.9 Schema path.

(2) Table specification

When more than one table is specified in the same SQL statement, table-specification indicates a qualifier that enables identification of a specific table to which a column, *, or row applies. The table name or a correlation name can be specified as the table specification.

When the same table is joined or the same table is specified in a subquery to reference columns in a table used in an outer query in that subquery, a correlation name can be used as an alias for those tables. A correlation name allows the user to use a table as two distinct tables.

table-specification::={[authorization-identifier.] table-identifier
                          | correlation-name | query-name}

If a query name specified in the WITH clause and the same table identifier as the query name are to be specified in a FROM clause in the query expression body of a query expression that uses a WITH clause, it is necessary to explicitly qualify the table identifier with an authorization identifier and to differentiate the name by specifying a correlation name for the query name and the table identifier.

If a name in a table specification for the query expression body of a query expression that uses a WITH clause is qualified by an authorization identifier, that name is treated as a table identifier; if the name is not qualified by an authorization identifier, that name is treated as a query name or a table identifier. Note that if the name is not qualified by an authorization identifier, the query name has a higher priority than the table identifier.

Examples of table specifications are shown below.

Example 1
The two tables STOCK and ORDERS both have a column named PCODE. To reference the correct PCODE column, its name is qualified with its table name (STOCK):

SELECT STOCK.PCODE,PNAME,CCODE
   FROM STOCK,ORDERS
   WHERE STOCK.PCODE=ORDERS.PCODE

Example 2
In a table created by joining two of the same table (e.g., STOCK tables), correlation names (X, Y) are used as qualifiers (products in the same color as product code 101M are searched):

SELECT X.* FROM STOCK X,STOCK Y
   WHERE X.COLOR=Y.COLOR AND Y.PCODE='101M'

Example 3
Correlation names simplify the description of lengthy table names (the name of the RDAREA storing a table (STOCK) belonging to the user is searched from a data dictionary table):

SELECT X.RDAREA_NAME
   FROM MASTER.SQL_RDAREAS X,
        MASTER.SQL_TABLES Y
   WHERE Y.TABLE_SCHEMA='U'
     AND Y.TABLE_NAME='STOCK'
     AND X.RDAREA_NAME=Y.RDAREA_NAME

Example 4
In a query expression using a WITH clause, specifying, in a FROM clause in the query expression itself, a table identifier (STOCK) which is identical to the query name used in the WITH clause (STOCK):

WITH STOCK(QC1,QC2)
 AS (SELECT PCODE,PRICE*SQUANTITY FROM STOCK)
   SELECT * FROM STOCK X,USER1.STOCK Y

(3) Column specification

In the following cases, a column name or repetition column name must be qualified with a specified table name; such a qualified column name or repetition column name is called a column specification:

(Incorrect)

Whether CLM1 specified in the selection expression is a column name of query name QRY1 or of query name QRY2 is not clear; the column name must be qualified:

WITH QRY1(CLM1) AS (SELECT PNAME FROM STOCK),
    QRY2(CLM1) AS (SELECT PCODE FROM STOCK)
    SELECT CLM1 FROM QRY1,QRY2

(Correct)

The column name of the specified column is qualified:

WITH QRY1(CLM1) AS (SELECT PNAME FROM STOCK),
    QRY2(CLM1) AS (SELECT PCODE FROM STOCK)
    SELECT QRY1.CLM1,QRY2.CLM1 FROM QRY1,QRY2

Some column names can be qualified while others cannot, due to syntactic considerations. The description column-specification in a format specification indicates a column name that can be qualified. The description column-name indicates that the column name cannot be qualified.

Column-specification::= [table-specification.]
{column-name|repetition-column-name[[subscript]]}

Subscript::={integer|ANY}

The following rules apply when a column name is qualified with a specified table name.

  1. A column name can be qualified with a specified correlation name or table name only within the scope of the correlation name or table name.
    For the scope of a correlation name or table name, see 2.6 Table reference, DELETE statement Format 1 (Delete rows) and UPDATE statement Format 1 (Update data) in Chapter 4.
  2. If a subquery specifies more than one valid table specification (correlation name or table name) with the same name, any reference to that name refers to the table specification (correlation name or table name) that was specified in the innermost query.
    The following is an example of specifying multiple, valid table names that have the same name:
    Example:
    In the subquery, both the T11 and T12 specifications are valid. Referencing T1 specifies T12 in the innermost query.

    SELECT * FROM T11
      WHERE T11.C2 >=
         (SELECT AVG(C2) FROM T12
          WHERE T12.C1 = 1      )

    Valid range of T11: SELECT*FROM T11 and subsequent lines
    Valid range of T12: (SELECT AVG (C2) FROM T12 and subsequent lines
    Tables having the most local scope (identically named tables occur in the same FROM clause) must be qualified with correlation names. The following is an example of identically named tables occurring in the same FROM clause:
    (Incorrect)
    This subquery, in which T11 and T12 which have the most local scope occur, contains an invalid scope specification. To avoid this error, specify appropriate correlation names.

    SELECT*FROM T2
      WHERE C3 IN
         (SELECT T11.C3 FROM T11,T12
          WHERE T11.C1=T12.C3   )

    Most local scope: (SELECT T11.C3 FROM T11, T12 and subsequent specifications
    (Correct)
    The specified column must exist in the table that is specified with a qualifier.

    SELECT*FROM T2
      WHERE C3 IN
         (SELECT X.C3 FROM T11 X,T12 Y
          WHERE X.C1=Y.C2          )

  3. Column names can be qualified with a table specification. If column names are unqualified, the subquery that has the most local scope must contain one table that contains the specified column.