15.00 - HELP TYPE - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

HELP TYPE

General Type Attributes

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” on page 901 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 release of Teradata Database.

    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.
  • Note: 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” on page 938).

    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” on page 901 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 “Working with Object Name and Title Data in HELP Reports” on page 896.

    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

    (continued)

    UDT SQL Name

    VARCHAR(644)

    Yes

    UDT Uescape

    VARCHAR(1)

    Yes