Names can be qualified by using periods to connect an RD-node name, authorization identifier, and a table identifier. Qualifying name is used to explicitly designate an authorization identifier or to make a given name unique.
Explanations of these names and their formats are given as follows:
table-name::=[[RD-node-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
Specification item | Description | Rules |
---|---|---|
Authorization identifier | When a specified table identifier, index identifier, index type identifier, data type identifier, routine identifier or trigger identifier is the user's own identifier, the user's authorization identifier should be specified. When any of these identifiers belongs to another user, that user's authorization identifier should be specified. However, when specifying the name of a public view in a table identifier, specify PUBLIC. | When the authorization identifier is omitted, the authorization identifier that is assumed as the default is as explained below: During execution of a utility program:
|
RD-node name | For access to a remote database, an RD-node name should be specified. When no RD-node name is specified, the access is a local access. | |
Table identifier | Specifies the name of a base table, foreign table, or view table. | |
Index identifier | Specifies the name of an index or foreign index. | |
Index type identifier | Specifies the name of an index type. | |
Data type identifier | Specifies the name of a user-defined type. | |
Routine identifier | Specifies the name of a procedure or function. | |
Trigger identifier | Specifies the name of a trigger. |
* 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:
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::={RD-node-name.authorization-identifier.table-identifier
| unqualified-table-specification}
unqualified-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.
SELECT STOCK.PCODE,PNAME,CCODE
FROM STOCK,ORDERS
WHERE STOCK.PCODE=ORDERS.PCODE
SELECT X.* FROM STOCK X,STOCK Y
WHERE X.COLOR=Y.COLOR AND Y.PCODE='101M'
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
WITH STOCK(QC1,QC2)
AS (SELECT PCODE,PRICE*SQUANTITY FROM STOCK)
SELECT * FROM STOCK X,USER1.STOCK Y
SELECT X.ENO
FROM OSAKA.USER1.EMP X
WHERE X.ENAME=N'John Doe'
In the following cases, it may be necessary to specify a column name or a repetition column name with an unqualified table specification; 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::= unqualified-table-specification.]
{column-name|repetition-column-name[[subscript]]}
Subscript::={integer|ANY}
Qualifying a column name with an unqualified table specification is subject to the following rules:
SELECT * FROM T11
WHERE T11.C2 >=
(SELECT AVG(C2) FROM T12
WHERE T12.C1 = 1 )
SELECT*FROM T2
WHERE C3 IN
(SELECT T11.C3 FROM T11,T12
WHERE T11.C1=T12.C3 )
SELECT*FROM T2
WHERE C3 IN
(SELECT X.C3 FROM T11 X,T12 Y
WHERE X.C1=Y.C2 )