Avoiding undefined or ambiguous references

The following situations are considered errors:

• No object table contains a column with the specified name. The reference is undefined.
• The column name is qualified by a table designator, but the table designated does not include a column with the specified name. Again the reference is undefined.
• The name is unqualified and more than one object table includes a column with that name. The reference is ambiguous.
• The column name is qualified by a table designator, but the table designated is not unique in the FROM clause and both occurrences of the designated table include the column. The reference is ambiguous.
• The column name is in a nested table expression which is not preceded by the LATERAL or TABLE keyword or a table function or nested table expression that is the right operand of a right outer join, full outer join, or a right exception join and the column name does not refer to a column of a table-reference within the nested table expression's fullselect. The reference is undefined.

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 default schema is CORPDATA, then:

      SELECT CORPDATA.EMPLOYEE.WORKDEPT
        FROM EMPLOYEE

   is a valid statement.
2. If the default schema 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 default schema 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.