17.10 - Example: SHOW For a SELECT Request That Accesses Multiple Views - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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;