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

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.