column_name - Advanced SQL Engine - Teradata Database

Specifies the name of one or more non-QITS columns, in the order they are to be defined for the table.

For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

You can define up to 2,048 columns, including the mandatory QITS column, for a queue table.

data type

You must specify a single data type for each column_name.

Queue tables cannot contain columns with BLOB or CLOB data types.

For information on data types, data type attributes, and converting between data types, see Teradata Vantage™ - Data Types and Literals, B035-1143.

If you do not specify explicit formatting, a column assumes the default format for the data type, which can be specified by a custom data formatting specification (SDF) defined by the tdlocaledef utility. See Teradata Vantage™ - Database Utilities , B035-1102 . Explicit formatting applies to the parsing and retrieval of character strings.

column attributes

One or more data definition phrases that define data for the column.

You cannot specify a character server data set of KANJI1. Otherwise, Vantage returns an error to the requestor.

Column data attribute specifications are optional. If you specify attributes for a column, you should define its data type prior to defining the attributes.

Example: Using SELECT AND CONSUME on Queue Tables with JSON, ST_GEOMETRY, or XML Columns

You can create queue tables with inline JSON, ST_GEOMETRY, or XML Columns, for example, JSON(1000). However, to retrieve data from this table using SELECT AND CONSUME, you must use non-LOB transforms.

Below is the table definition for this example.

CREATE SET TABLE qt1, QUEUE
(
      QITS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      col1 INTEGER,
      jsn JSON(64000) CHARACTER SET LATIN
)
PRIMARY INDEX (col1);

Then, we insert a row of data.

INSERT INTO qt1 VALUES (current_timestamp, 1, '{"a":123}');

Then, we create a user with non-LOB transforms for JSON, ST_GEOMETRY, and XML data.

CREATE USER User1 AS PERM=1e8 * (HASHAMP () + 1), PASSWORD=secret,
    TRANSFORM ( JSON CHARACTER SET LATIN=TD_JSON_VARCHAR,
                ST_GEOMETRY=TD_GEO_VARCHAR,
                XML=TD_XML_VARCHAR);

Now, log in as User1 to retrieve the data.

SELECT AND CONSUME TOP 1 col1, jsn FROM qt1;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.
       col1 jsn
----------- ---------------------------------------------------------------
          1 {"a":123}