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

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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;