16.20 - Restrictions on Consume Mode SELECT Statements - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
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