17.10 - Restrictions on Consume Mode SELECT Statements - 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)
The following restrictions apply to SELECT AND CONSUME TOP 1 statements. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • You cannot join queue tables with other tables in a SELECT AND CONSUME statement.

    The workaround for this is to perform an INSERT … SELECT operation to copy the rows from the queue table you want to join with another table into a non-queue table, then join that non-queue table with the other table.

    For example:

    DELETE ALL FROM tempTbl;
    INSERT INTO tempTbl
    SELECT AND CONSUME TOP 1 * FROM qTbl;
    SELECT c.name, t.customerId, t.orderId
    FROM tempTbl AS t, customer AS c
    WHERE t.customerId = c.customerId;
  • You cannot specify a WHERE clause in a SELECT AND CONSUME statement.
  • You cannot specify aggregate or ordered analytic functions in a SELECT AND CONSUME statement.
  • The mandatory TOP n clause cannot be specified for any case other than n=1.
  • SELECT AND CONSUME statements do not support the PERCENT or WITH TIES options for the TOP n clause. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

You cannot specify a SELECT AND CONSUME statement in a:

  • Subquery
  • Search condition
  • Logical predicate
  • Set operator