HELP TYPE Statement | Teradata Vantage - 17.10 - General Type Attributes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The following table lists the attributes reported by HELP TYPE with no options:

Attribute Data Type Nullable? Description
Name VARCHAR(61) No The name of the UDT or ARRAY/VARRAY type.
Internal Type CHARACTER(2) No A code for the UDT or ARRAY/VARRAY type.
  • A1 is a one-dimensional ARRAY/VARRAY.
  • AN is a multidimensional ARRAY/VARRAY.
  • UD is a distinct UDT.
  • US is a structured UDT.
  • UT is a Teradata internal UDT.

The external type for both A1 and AN internal types is always CV (VARCHAR).

External Type CHARACTER(2) Yes. The external data type of the UDT or ARRAY/VARRAY as defined by its fromsql transform.

See Data Type Codes for a list of the data type codes and their meanings.

A null is reported if the UDT or ARRAY/VARRAY has no fromsql transform defined for it.

With an explicit format, DATE is imported and exported according to that format, as indicated in the following bullets.
  • With an Implicit format created in INTEGERDATE mode, the export format is ‘YY/MM/DD’.
  • With an Explicit format created in ANSIDATE mode, the export format is ‘YYYY-MM-DD’.

MaxLength is 4, representing the internal storage size.

Max Length INTEGER Formatted as 
-(10)9 Yes Maximum length of the external type in bytes.

For CHARACTER data, Max Length is the length of the column declaration in bytes.

For UDTs and ARRAY/VARRAY types, Max Length is the maximum transformed length of the external type associated with the UDT formatted as -(10)9.

You can use a CLIv2 StatementInfo parcel to return the description of items within the Data Field of the corresponding indicator mode Record parcels.

For example, the FastLoad .TABLE command can be executed in Indicator Mode to get the additional information of data type and length of all table attributes. That information can then be used to fetch the data correctly in any database release.

Decimal Total Digits SMALLINT Yes Only reported for numeric data types, otherwise is null.

For INTERVAL types, returns a value from 1 - 4 representing the number of digits in the leading field of the interval (number of non-fractional digits for INTERVAL SECOND).

This column is null for other TIME and TIMESTAMP data types.

Regardless of the underlying data type, ARRAY/VARRAY types always return null for this attribute.

Decimal Fractional Digits SMALLINT Yes Only reported for numeric data types, otherwise is null.

For TIME and TIMESTAMP types and all INTERVAL types with a SECOND field, this column returns a value of 0 - 6, indicating the fractional precision of seconds. For INTERVAL types without a SECOND field, the column returns null.

The character data types Latin, Unicode, Graphic, KanjiSJIS, and Kanji1 are distinguished with respect to CF and CV by their respective value for the Character Type column.

Regardless of the underlying data type, ARRAY/VARRAY types always return null for this attribute.

Contains LOB CHARACTER(1) No Records whether the UDT contains BLOB or CLOB predefined data types.
  • N specifies that the UDT does not contain a LOB data type.
  • Y specifies that the UDT contains a LOB data type.
Ordering CHARACTER(1) No Records whether the UDT has a defined ordering.
  • E specifies that only equality and non-equality comparisons are supported for the UDT.

    This code is only supported for Teradata internal UDTs.

  • F specifies that all comparison operations are supported for the UDT.
  • N specifies that the UDT does not have a defined ordering.
Ordering Category CHARACTER(1) Yes Records the ordering category for the UDT.
  • M specifies that the UDT is mapped, meaning it has a defined ordering.
  • R specifies that the UDT ordering is relative.

    This code is only supported for Teradata internal UDTs.

  • Null specifies that the UDT does not have a defined ordering.
Ordering Routine VARCHAR(61) Yes The type of ordering routine is one of the following:
  • For user-defined routines, the fully qualified name of the external routine that provides ordering functionality in the form database.object.

    If the value is not a fully qualified name, the system returns NULL for the 6 naming attributes beginning with Dictionary Ordering Routine Database, shown later in this table.

  • S for a system ordering routine
  • L for a local ordering routine

The column is null if a UDT does not have a defined ordering.

Cast CHARACTER(1) No Records whether at least one cast is defined for the UDT.
  • N specifies that no casts are defined for the UDT.
  • Y specifies that casts are defined for the UDT.
Transform CHARACTER(1) No Records whether a transform is defined for the UDT or not.
  • N specifies that no transform is defined for the UDT.
  • Y specifies that a transform is defined for the UDT.
Method CHARACTER(1) Yes Indicates whether methods are associated with this UDT.
  • N specifies that no methods are associated with the UDT.
  • Y specifies that at least one method is associated with the UDT.

Structured UDTs always report Y for this column because of their system-generated instance and mutator methods.

No method names are reported with HELP TYPE.

To report a complete list of the methods associated with a UDT, use the HELP METHOD statement (see HELP METHOD).

Char Type CHARACTER(1) No Returns the character data type for a CHAR or VARCHAR column.
  • 1 specifies LATIN server character data type columns.
  • 2 specifies UNICODE server character data type columns.
  • 3 specifies KANJISJIS server character data type columns.
  • 4 specifies GRAPHIC server character data type columns.
  • 5 specifies KANJI1 server character data type columns.

If the data type of the column is not character, returns a null.

Array (Y/N) CHARACTER(1) No Whether the UDT is an ARRAY type.
  • N specifies that the UDT is not an ARRAY type.
  • Y specifies that the UDT is an ARRAY type.
Dimensions BYTEINT formatted as --9 Yes Returns the number of dimensions the ARRAY type has.
  • For a one-dimensional ARRAY type (Internal Type code A1), the Dimensions value is always 1.
  • For a multidimensional ARRAY type (Internal Type code AN), the Dimensions value is an integer in the range 1-5, inclusive.
Element Type CHARACTER(2) Yes Returns the data type for the ARRAY element.

See Data Type Codes for a list of the possible data type codes that can be reported for Element.

UDT Name VARCHAR(61) Yes Returns the qualified name of the UDT that is the element type of the ARRAY type if the element type of the array is a UDT.

The server character set specification for the column is CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC.

Array Scope VARCHAR(3200) formatted as X(45) Yes Returns the scope of the array for an ARRAY type in the form [n:m] for a one-dimensional array and [n:m] … [n:m] for a multidimensional array, where n is the lower bound for each dimension of the type and m is the upper bound for each dimension of the type.

To see more than 45 characters, use record mode.

The server character set specification for the column is CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC.

For a one-dimensional array, the lower bound always begins with 1. The bound ends with m, where m is the maximum cardinality of the array.

For a multidimensional array, the string contains the scope information specified for the type when it was created.

If explicit lower bounds were not specified for the multidimensional array when it was created, then the lower bound always begins with 1, and ends with m, where m is the maximum cardinality of the array.

Dictionary Name VARCHAR(128) No The attributes shown in this section are based upon and supersede the corresponding older Name, Ordering Routine, and UDT Name attributes, while providing additional functionality with several new attributes.

The older attributes are retained for compatibility with existing applications.

For details, see the topics beginning with Object Name and Title Data in HELP Reports.

SQL Name VARCHAR(644) No
Uescape VARCHAR(1) Yes
Dictionary Ordering Routine Database VARCHAR(128) Yes
SQL Ordering Routine Database VARCHAR(644) Yes
Ordering Routine Database Uescape VARCHAR(1) Yes
Dictionary Ordering Routine VARCHAR(128) Yes
SQL Ordering Routine VARCHAR(644) Yes
Ordering Routine Uescape VARCHAR(1) Yes
UDT Database Dictionary Name VARCHAR(128) Yes
UDT Database SQL Name VARCHAR(644) Yes
UDT Database Name Uescape VARCHAR(1) Yes
UDT Dictionary Name VARCHAR(128) Yes
UDT SQL Name VARCHAR(644) Yes
UDT Uescape VARCHAR(1) Yes