Handling of Rows from a Queue Table | CREATE TABLE | Teradata Vantage - Rules for Consuming Rows From a Queue - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
The following rules and recommendations apply to consuming rows from a queue table (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for a complete description of the rules and limits for consuming queue table rows).
  • To consume rows from a queue table, specify the AND CONSUME TOP 1 keywords in your SELECT statement. The AND CONSUME keywords indicate that the request is a consume mode request, while TOP 1 indicates that the oldest row from the queue table is to be retrieved.

    The following example consumes a row from a queue table named shopping_cart.

    SELECT AND CONSUME TOP 1 *
    FROM shopping_cart;
  • You can also consume rows from stored procedures and embedded SQL applications that use the INTO clause to assign the values from a row to host or local variables.
  • You should place any action taken based on consuming a row from a queue table in the same transaction as the consume mode SELECT operation on that same queue table. This ensures that both the row consumption and the action taken on that queue table are committed together, so no row or action for that queue table is lost.

    If no action is to be taken, then you should isolate any SELECT AND CONSUME statement as the only request in a transaction. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

  • A multistatement request should contain only 1 consume mode SELECT statement.
  • A trigger can call a stored procedure that consumes a row from a queue table.