Reputation: 674

There is a system table in Teradata where I can find the full name of column types?

I'm using Teradata 14.10 and I haven't found a system table that contains the full name of data types used in the field ColumnTypes in the tables DBC.Columns and DBC.ColumnsVX

I've found on this forum the mapping from ColumnTypes codes to their full name, but I would prefer not do the mapping manually.

Upvotes: 1

Answers (3)

dnoeth

Reputation: 60462

There's no official table, but it can easily be created from the mapping provided in the Data Dictionary manual (this is where the list in the DevEx-link was copied from).

But I would prefer a simple SQL UDF to do the mapping, as the list of possible values is quite stable (in a new TD release you might have to either insert a new row or add a new WHEN condition):

REPLACE FUNCTION Datatype
 (
  ColumnType CHAR(2)
 )
RETURNS VARCHAR(60)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
  CASE ColumnType
    WHEN 'BF' THEN 'BYTE'
    WHEN 'BV' THEN 'VARBYTE'
    WHEN 'CF' THEN 'CHAR'
    WHEN 'CV' THEN 'VARCHAR'
    WHEN 'D ' THEN 'DECIMAL'
    WHEN 'DA' THEN 'DATE'
    WHEN 'F ' THEN 'FLOAT'
    WHEN 'I1' THEN 'BYTEINT'
    WHEN 'I2' THEN 'SMALLINT'
    WHEN 'I8' THEN 'BIGINT'
    WHEN 'I ' THEN 'INTEGER'
    WHEN 'AT' THEN 'TIME'
    WHEN 'TS' THEN 'TIMESTAMP'
    WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
    WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
    WHEN 'YR' THEN 'INTERVAL YEAR'
    WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
    WHEN 'MO' THEN 'INTERVAL MONTH'
    WHEN 'DY' THEN 'INTERVAL DAY'
    WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
    WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
    WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
    WHEN 'HR' THEN 'INTERVAL HOUR'
    WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
    WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
    WHEN 'MI' THEN 'INTERVAL MINUTE'
    WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
    WHEN 'SC' THEN 'INTERVAL SECOND'
    WHEN 'BO' THEN 'BLOB'
    WHEN 'CO' THEN 'CLOB'
    WHEN 'PD' THEN 'PERIOD(DATE)'     
    WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
    WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
    WHEN 'PT' THEN 'PERIOD(TIME)'
    WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
    WHEN 'UT' THEN 'User Defined Datatype'
    WHEN '++' THEN 'TD_ANYTYPE'
    WHEN 'N'  THEN 'NUMBER'
    WHEN 'A1' THEN 'ARRAY'
    WHEN 'AN' THEN 'ARRAY (multidimensional'
    WHEN 'JN' THEN 'JSON'
    WHEN 'VA' THEN 'TD_VALIST'
    WHEN 'XM' THEN 'XML'
    ELSE '<Unknown> ' 
  END;

You might also use this SQL-UDF to get the full datatype (I didn't add the new 15.10 JSON-variants BSON & UBJSON, yet):

/**********
Returns the datatype of a column as a literal

20111012 initial version - Dieter Noeth
20131010 added TD14.10 ARRAY datatypes - dn
20140812 added TD15 JSON / TD_VALIST datatype - dn
**********/
REPLACE FUNCTION DataTypeString 
 (
  ColumnType CHAR(2),
  ColumnLength INT,
  DecimalTotalDigits SMALLINT,
  DecimalFractionalDigits SMALLINT,
  CharType SMALLINT,
  ColumnUDTName VARCHAR(128) CHARACTER SET UNICODE
 )
RETURNS VARCHAR(60)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
  CASE ColumnType
    WHEN 'BF' THEN 'BYTE('            || TRIM(ColumnLength (FORMAT '-(9)9')) || ')'
    WHEN 'BV' THEN 'VARBYTE('         || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
    WHEN 'CF' THEN 'CHAR('            || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
    WHEN 'CV' THEN 'VARCHAR('         || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
    WHEN 'D ' THEN 'DECIMAL('         || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ','
                                      || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'DA' THEN 'DATE'
    WHEN 'F ' THEN 'FLOAT'
    WHEN 'I1' THEN 'BYTEINT'
    WHEN 'I2' THEN 'SMALLINT'
    WHEN 'I8' THEN 'BIGINT'
    WHEN 'I ' THEN 'INTEGER'
    WHEN 'AT' THEN 'TIME('            || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'TS' THEN 'TIMESTAMP('       || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'TZ' THEN 'TIME('            || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE'
    WHEN 'SZ' THEN 'TIMESTAMP('       || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE'
    WHEN 'YR' THEN 'INTERVAL YEAR('   || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'YM' THEN 'INTERVAL YEAR('   || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO MONTH'
    WHEN 'MO' THEN 'INTERVAL MONTH('  || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'DY' THEN 'INTERVAL DAY('    || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'DH' THEN 'INTERVAL DAY('    || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO HOUR'
    WHEN 'DM' THEN 'INTERVAL DAY('    || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO MINUTE'
    WHEN 'DS' THEN 'INTERVAL DAY('    || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO SECOND('
                                      || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'HR' THEN 'INTERVAL HOUR('   || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'HM' THEN 'INTERVAL HOUR('   || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO MINUTE'
    WHEN 'HS' THEN 'INTERVAL HOUR('   || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO SECOND('
                                      || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'MI' THEN 'INTERVAL MINUTE(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'MS' THEN 'INTERVAL MINUTE(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ')'      || ' TO SECOND('
                                      || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'SC' THEN 'INTERVAL SECOND(' || TRIM(DecimalTotalDigits (FORMAT '-(9)9')) || ',' 
                                      || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')'
    WHEN 'BO' THEN 'BLOB('            || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
    WHEN 'CO' THEN 'CLOB('            || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'

    WHEN 'PD' THEN 'PERIOD(DATE)'     
    WHEN 'PM' THEN 'PERIOD(TIMESTAMP('|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE)'
    WHEN 'PS' THEN 'PERIOD(TIMESTAMP('|| TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || '))'
    WHEN 'PT' THEN 'PERIOD(TIME('     || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || '))'
    WHEN 'PZ' THEN 'PERIOD(TIME('     || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) || ')' || ' WITH TIME ZONE)'
    WHEN 'UT' THEN COALESCE(ColumnUDTName,  '<Unknown> ' || ColumnType)

    WHEN '++' THEN 'TD_ANYTYPE'
    WHEN 'N'  THEN 'NUMBER('          || CASE WHEN DecimalTotalDigits = -128 THEN '*' ELSE TRIM(DecimalTotalDigits (FORMAT '-(9)9')) END
                                      || CASE WHEN DecimalFractionalDigits IN (0, -128) THEN '' ELSE ',' || TRIM(DecimalFractionalDigits (FORMAT '-(9)9')) END
                                      || ')'
    WHEN 'A1' THEN COALESCE('SYSUDTLIB.' || ColumnUDTName,  '<Unknown> ' || ColumnType)
    WHEN 'AN' THEN COALESCE('SYSUDTLIB.' || ColumnUDTName,  '<Unknown> ' || ColumnType)

    WHEN 'JN' THEN 'JSON('            || TRIM(ColumnLength (FORMAT 'Z(9)9')) || ')'
    WHEN 'VA' THEN 'TD_VALIST'
    WHEN 'XM' THEN 'XML'

    ELSE '<Unknown> ' || ColumnType
  END 
  || CASE
        WHEN ColumnType IN ('CV', 'CF', 'CO') 
        THEN CASE CharType 
                WHEN 1 THEN ' CHARACTER SET LATIN'
                WHEN 2 THEN ' CHARACTER SET UNICODE'
                WHEN 3 THEN ' CHARACTER SET KANJISJIS'
                WHEN 4 THEN ' CHARACTER SET GRAPHIC'
                WHEN 5 THEN ' CHARACTER SET KANJI1'
                ELSE ''
             END
         ELSE ''
      END
;


SELECT
   DatabaseName,
   TableName,
   ColumnName,

   DataTypeString
    (ColumnType,
     ColumnLength,
     DecimalTotalDigits,
     DecimalFractionalDigits,
     CharType,
     ColumnUDTName),

   ColumnType,
   ColumnLength,
   DecimalTotalDigits,
   DecimalFractionalDigits,
   CharType
FROM dbc.ColumnsV
WHERE ColumnType IS NOT NULL
ORDER BY 4
ORDER BY 1,2,3
;

Upvotes: 2

xenodevil

Reputation: 604

I'm not aware of any table in DBC which contains this information, but you can find this information in Teradata Documentation (and perhaps load it in a table of your own).

Data Dictionary document specifies all possible values for ColumnType in DBC.Columns. I have listed the start of that list here, in case it is of some help, from Documentation for TD 15.10 (chapter 3, pg. 94).

Value   Description
----------------------------------------------------------
++      TD_ANYTYPE
A1      One dimensional ARRAY data type
AT      ANSI Time
BF      BYTE Fixed
BO      Byte Large Object

I haven't checked the backwards compatibility, but it almost definitely is backwards compatible (no reason why it shouldn't be). Same document contains a lot of other useful information about possible value in a large number of DBC objects.

Upvotes: 0

access_granted

Reputation: 1907

SELECT 
  trim(ColumnName) ||
  case columnType
    when 'CV' then ' varchar(' || trim(ColumnLength) || ') '
    when 'DA' then ' date '
    when 'F' then ' float '
    when 'N' then ' number '
    when 'D' then ' decimal(' || trim(DecimalTotalDigits) || ',' || trim(DecimalFractionalDigits) || ') '
    when 'TS' then ' timestamp( ' || trim(ColumnLength) || ') '
    when 'AT' then ' time '
    when 'CF' then ' char(' || trim(ColumnLength) || ') '
  end ColumnDef
FROM DBC.columns s1
WHERE databasename='$database' and tablename='$table';

Upvotes: 0

There is a system table in Teradata where I can find the full name of column types?

Answers (3)

Related Questions