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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;