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.
Explanations of these names and their formats are given as follows:
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
Item | Specification | Rules |
---|---|---|
Authorization identifier | If 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:
|
Table identifier | Specify the name of a base table or view table. | |
Index identifier | Specify the name of an index. | |
Index type identifier | Specify the name of an index type. | |
Data type identifier | Specify the name of a user-defined type. | |
Routine identifier | Specify the name of a procedure or function. | |
Trigger identifier | Specify the name of a trigger. | |
Sequence generator identifier | Specify the name of a sequence generator. |
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.
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
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.
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 )