16.20 - Example: Specifying a View Column Name - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.