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

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.

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);

Insert a row of data:

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

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);

Log on 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}