Example: SHOW For a SELECT Request That Accesses Multiple Views - Teradata Vantage - Analytics Database

You create a view using two other views, each having the same name, but created in different databases.

The database names are US and International. The session is in Teradata mode.

Here is the DDL used to create the objects used for this example.

     DATABASE us;
     CREATE TABLE table_1 (
       eno INTEGER);
     CREATE VIEW view_1 AS
      SELECT *
      FROM table_1;
     DATABASE international;
     CREATE TABLE table_1 (
       zno INTEGER);
     CREATE VIEW view_1 AS
      SELECT *
      FROM table_1;
     CREATE VIEW view_3 AS
      SELECT *
      FROM international.view_1, us.view_1;

If you perform a SHOW request on a query that selects data from view_3 , the report it returns will be ambiguous with respect to the two view_1 elements of view_3 unless you specify the QUALIFIED option.

For example, suppose you perform SHOW on the following query:

     SHOW SELECT * 
          FROM view_3;

The report looks like this:

     *** Text of DDL statement returned.
     *** Total elapsed time was 1 second.
     -------------------------------------------------------------
     CREATE SET TABLE international.table_1, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (zno INTEGER)
     PRIMARY INDEX (a);
     *** Text of DDL statement returned.
     -------------------------------------------------------------
     CREATE SET TABLE us.table_1, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (eno INTEGER)
     PRIMARY INDEX (eno);
     *** Text of DDL statement returned.
     -------------------------------------------------------------
     CREATE VIEW view_1 AS SELECT * FROM table_1;
     *** Text of DDL statement returned.
     -------------------------------------------------------------
     CREATE VIEW view_1 AS SELECT * FROM table_1;
     *** Text of DDL statement returned.
     -------------------------------------------------------------
     CREATE VIEW view_3 AS SELECT * FROM international.view_1,us.view_1;