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.