When you qualify a column with a table name, the qualifying column cannot be a named object. The following examples demonstrate why this does not work.
Begin with table t, defined as:
CREATE TABLE T (a INT, b INT);
Using this base table, note how the SQL resolver parses and resolves the following statement, creating a view based on the base table:
CREATE VIEW V AS SELECT T.a (NAMED X), X (NAMED Y);
Name order and resolution are extremely important because of the way the resolver treats this statement.
|Step||IF Column x is …||THEN …||ELSE …|
|1||in table t||proceed|
|2||not in table t||find column x in view v||display an appropriate error message|
The following example explains why named objects can be tricky to use in statements such as this:
CREATE VIEW V AS SELECT a*5+3 (NAMED X), x*2 (NAMED Y) FROM T;
The Resolver analyzes this statement as follows:
- Look for X as a column in table T.
- If X is not found, then try to locate column X in view V (the named object).
Usage of named objects is important, as shown in the following example:
CREATE VIEW V AS SELECT a (NAMED b), b (NAMED y) FROM T;
Note that the phrase b (NAMED Y) resolves to T.b.
The following example does not work because the table named T does not have a column named X. X is a named object, not a column name. The erroneous example is:
CREATE VIEW v AS SELECT t.a (NAMED X), t.x (NAMED Y);
Because table T does not have a column named X, the statement fails. X is a named object. The rule is that when you qualify a column with a table name, the qualifying column cannot be a named object.