Avoiding undefined or ambiguous references

When a column name refers to values of a column, it must be possible to resolve that column name to exactly one object table.

The following situations are considered errors:

Avoid ambiguous references by qualifying a column name with a uniquely defined table designator. If the column is contained in several object tables with different names, the object table names can be used as designators. Ambiguous references can also be avoided without the use of the table designator by giving unique names to the columns of one of the object tables using the column name list following the correlation name.

When qualifying a column with the exposed table name form of a table designator, either the qualified or unqualified form of the exposed table name may be used. However, the qualifier used and the table used must be the same after fully qualifying the table name or view name and the table designator.

  1. If the authorization ID of the statement is CORPDATA, then:
       SELECT CORPDATA.EMPLOYEE.WORKDEPT
         FROM EMPLOYEE
    is a valid statement.
  2. If the authorization ID of the statement is REGION, then:
       SELECT CORPDATA.EMPLOYEE.WORKDEPT
         FROM EMPLOYEE                             ***INCORRECT***
    is invalid, because EMPLOYEE represents the table REGION.EMPLOYEE, but the qualifier for WORKDEPT represents a different table, CORPDATA.EMPLOYEE.
  3. If the authorization ID of the statement is REGION, then:
       SELECT EMPLOYEE.WORKDEPT
         FROM CORPDATA.EMPLOYEE                             ***INCORRECT***
    is invalid, because EMPLOYEE in the select list represents the table REGION.EMPLOYEE, but the explicitly qualified table name in the FROM clause represents a different table, CORPDATA.EMPLOYEE. In this case, either omit the table qualifier in the select list, or define a correlation name for the table designator in the FROM clause and use that correlation name as the qualifier for column names in the statement.