16.20 - Examples - 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

Example 1: If User Default Server Character Set is LATIN

Consider the Name column in the following table definition:

   CREATE TABLE Employee
     (EmployeeID INTEGER
     ,Name       CHARACTER(30) CHARACTER SET LATIN
     ,Salary     DECIMAL(8,2));

If the user default server character set is LATIN, then the character string that TYPE returns for the Name column does not contain the CHARACTER SET phrase.

   SELECT TYPE(Employee.Name);
   
   Type(Name)
   ----------
   CHAR(30)

Example 2: If User Default Server Character Set is LATIN but Server Character Set for Name Column is UNICODE

If the user default server character set is LATIN, but the server character set for the Name column is UNICODE, then the result string contains the CHARACTER SET phrase.

   CREATE TABLE Employee
     (EmployeeID INTEGER
     ,Name VARCHAR(30) CHARACTER SET UNICODE
     ,Salary     DECIMAL(8,2));
   
   SELECT TYPE(Employee.Name);
   
   Type(Name)
   ---------------------------------
   VARCHAR(30) CHARACTER SET UNICODE

Example 3: Returning the types of the Name and Salary columns

The following statement returns the types of the Name and Salary columns:

   SELECT TYPE(Employee.Name), TYPE(Employee.Salary);
   Type(Name)   Type(Salary)
   -----------  ------------
   VARCHAR(30)  DECIMAL(8,2) 
   

Example 4: Using TYPE to Request the Data Type of Two Columns

If TYPE is used to request the data type of two columns, defined as GRAPHIC and LONG VARCHAR CHARACTER SET GRAPHIC, respectively, the result is as follows.

  TYPE(GColName)                 TYPE(LVGColName)
  -----------------------------  ------------------------------------
  CHAR(4) CHARACTER SET GRAPHIC  VARCHAR(32000) CHARACTER SET GRAPHIC

In the case of a LONG VARCHAR CHARACTER SET GRAPHIC column, the length returned is the maximum length of 32000.

Example 5: Using the TYPE Function

Consider the following TYPE function.

   SELECT TYPE(SUBSTR(Employee.Name,3,2));

The result type of SUBSTR depends on the session mode.

If the session is set to ANSI mode, the returned result is as follows:

   Type(Substr(Name,3,2))
   ----------------------
   VARCHAR(30)

If the session is set to Teradata mode, the returned result is as follows:

   Type(Substr(Name,3,2))
   ----------------------
   VARCHAR(2)

Example 6: Applying the TYPE function to the BLOB Column

Consider the following table definition:

   CREATE TABLE images
     (imageid INTEGER
     ,imagedesc VA
RCHAR(50)
     ,image BLOB(2K))
   UNIQUE PRIMARY INDEX (imageid);

The following statement applies the TYPE function to the BLOB column:

   SELECT TYPE(images.image) FROM images;

The result is:

   Type(image)
   -----------
   BLOB(2048)

Note that the result is a normal integer length, and does not use the K option that was used to define the BLOB column the CREATE TABLE statement.