16.20 - Example: Specifying a View Column Name - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

Consider the DBC.HostsInfo system view, which has the following definition:

   REPLACE VIEW DBC.HostsInfo (LogicalHostId, HostName, DefaultCharSet)
   AS SELECT
       LogicalHostId
      ,HostName
      ,DefaultCharSet
   FROM DBC.Hosts WITH CHECK OPTION;

The underlying table, DBC.Hosts, has the following definition:

   CREATE SET TABLE DBC.Hosts, FALLBACK, NO BEFORE JOURNAL, 
   NO AFTER JOURNAL, CHECKSUM = DEFAULT
      (LogicalHostId SMALLINT FORMAT 'ZZZ9' NOT NULL
      ,HostName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL
      ,DefaultCharSet VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC 
         NOT NULL)
   UNIQUE PRIMARY INDEX (LogicalHostId)
   UNIQUE INDEX (HostName);

The following statement uses the DEFAULT function with the DBC.HostsInfo.HostName view column name:

   SELECT DISTINCT DEFAULT(HostName) FROM DBC.HostsInfo;

The result of the DEFAULT function is null because the HostName view column is derived from a table column that has no explicit default value.