Browsing a Queue Table | CREATE TABLE | Teradata Vantage - 17.10 - Browsing a Queue Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

To browse a queue table is to select from it as you would a non-queue table.

The following list of queries provides some peeks at a queue without consuming any rows.
  • You can determine the queue depth of a queue table by using the COUNT(*) aggregate function, as follows.
    SELECT COUNT(*)
    FROM myqueue;

    If the depth is zero, then the system places a consume mode SELECT statement into a delayed state.

  • You can peek at the next row set that would be consumed from the queue table using the following query.
    SELECT *
    FROM shoppingcart
    WHERE qits = (SELECT MIN(qits)
                  FROM shoppingcart);
  • The following query selects only the next ten rows to be consumed.
    SELECT TOP 10 *
    FROM myqueue
    ORDER BY QITS;
  • The following query, a browse mode SELECT statement, returns the entire queue in FIFO order.
    SELECT *
    FROM myqueue
    ORDER BY myqueue_qits;
  • The duration of a queue table measures how old the top queue table row is.

    To determine the duration of a queue table, you can execute the following statement:

    SELECT TOP 1 CURRENT_TIMESTAMP, qits, (CURRENT_TIMESTAMP - qits)       DAY(4) TO SECOND AS queue_duration
    FROM shoppingcart
    ORDER BY qits;

    This query generates a report similar to the following:

        *** Query completed. One row found. 3 columns returned.
    Current Timestamp (6)  2004-05-19 14:30:01.420000+00:00
    Q Insertion Time   2004-05-18 08:44:19.460000
    queue_duration      1 05:45:41.960000